Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

Changing The Page Setup For Multiple Worksheets In A Workbook

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am trying to change the page set up setiings to fit to 1 page for 56
worksheets within a workbook. Is there an easy way to do this? Can I buy a
program add in to do this for me?

View Answers     

Similar Excel Tutorials

Scale Data for Printing in Excel - Fit more onto a Printed Page
This tip shows you how to fit more Excel data onto a page for printing. This does not change the size, shape, or lo ...
Insert and Manage Page Breaks in Excel
How to insert, remove, and manage page breaks in Excel.  This can be rather annoying and confusing but this tutori ...
Print All Worksheets at Once in Excel
How to print all worksheets at once from Excel. This saves you the time of having to go to each sheet individually ...
Set a Photo/Image as an Excel Worksheet Background
One of the really cool formatting features of Excel is that you can actually set a picture from your computer as t ...

Helpful Excel Macros

Print Preview Screen Display for The Current or Selected Worksheets in Excel
- This free Excel macro allows you to display the print preview screen or window for the current or selected worksheets in
Print Specific Pages in Excel
- This free Excel macro allows you to print a pre-specified selection of pages from Excel. This means you can print 2 pag
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web

Similar Topics

Is there a macro I can write that will just change the footers on my worksheets and leave everything else intact? I need to change the footers on all the worksheets in my workbook, so that they all look the same (file name, worksheet name, and date). But if I make all the worksheets active, and then just modify the first worksheet, all of the other "page setup" information is also copied to the other open worksheets. Some of the worksheets need to be portrait, others landscape, some print on one page vs multiple pages, etc.

I have over a hundred worksheets in my workbook. I have to change each worksheet to 69%, legal size, change all margins to 0 and set to landscape. Is there a formula or something that you can use so that you can set up all worksheets at once to save that page set up?

Hi All,

I have a workbook, which has numerous worksheets. On each worksheet is a screen print, not excel data but a CTRL+print Screen of another system. All are the same size. I want to print the whole workbook, each worksheet on one page, but at the moment the only way I know how to do this is to go into each worksheet and go File>Page Setup>Fit to 1 page tall by 1 page wide etc... is there a way to apply this change to the whole workbook, without going through each worksheet one by one??


I'm working with a user who has about 32 worksheets in an excel workbook. She
doesn't want the first page numbered and wants the next three pages to be
"index" numbered, followed by the "body" to start at page 1. Every time I
select multiple worksheets and go to File -> Page Setup -> Headers & Footers,
and I create a footer for page number, it's showing up as page 33, 32, and 31
(in that order). I'm confused. Can anyone help me with this task?
Sarah M.

If I have 10 worksheets in my file and I select all of them and then do a print preview, I can see each page - one at a time. If while in print preview I select the Setup... button, I can change the page setup parameters for just the page I'm viewing at that moment. Is there any way to make a mass change so ALL worksheets in a file get the same Page setup options?

I have a workbook with many worksheets (about 30). Some of the worksheets are formatted to print on two pages, others are only 1 page long. I want to number all of the pages. I am selecting all worksheets and through the page setup screen, I am checking off on the page # footer.

Instead of numbering all of the pages sequentially, Excel is numbering them 1,2,1,1,2,1,2,1,1 etc. instead of 1,2,3,4,5,6,7,8 etc. Can you help?

I've been doing a lot of searching to find out how to achieve the sequential numbering across multible worksheets, untill I finally found a post with a solution to the issue, which is to select the sheets one wants to print, and then in the page setup, adding "Page &[Page] of &[Pages]" (I'm not a 100% sure if that what they're called in english, as I'm using a Danish version) and then look at the print preview.
The outputs on the footers a
Page 1 of 5 (sheet 1)
Page 1 of 5 (sheet 2)
Page 2 of 5 (sheet 2)
Page 1 of 5 (sheet 3)
Page 1 of 5 (sheet 4)
Also, when attempting to offset the page-count in the page setup for a single sheet I get e.g.:
Page 2 of 3
Page 3 of 3
Page 4 of 3

Does anyone else have these problems?
Am I doing something wrong? And are there any workarounds or VBA solutions to these issues?

Hi All,
I have a single workbook containing 3 worksheets, each worksheet contains approx 5 to 10 pages but this varies.

Worksheet 1 contains barter invoices
Worksheet 2 contains quotes
Worksheet 3 contians contracts

I need to print all 3 of the above to one pdf file but with page numbers assigned to just the final worksheet.

If I try adding a footer with page numbers to the final worksheet only I achieve half the goal in so much as there are no page numbers on worksheets 1 and 2 but the page numbers on worksheet 3 read

Page 21 of 30
Page 22 of 30

When I need them to read

Page 1 of 10
Page 2 of 10

And if I switch worksheet 3 with worksheet one I still end up with...

Page 1 of 30
Page 2 of 30

Any ideas on how I can get the page numbering to focus on one worksheet only whilst printing multiple worksheets?

This may be a simple question, I was sent a work book with 20+ worksheets (tabs), it appears that each sheet is formatted different (landscape / portiait) I need to print each page, so is there a way I can formatt all tabs within the workbook at one time. I eed each page to be setup as landscape?

I need to insert a header on each worksheet in my workbook with out changing the page layouts. I know you can group the worksheets together, then under page setup insert a header, but that conforms each worksheet to the same page layout, i.e. portrait or landscape. My workbook has both portrait and landscape worksheets, how can I place a header on each worksheet without changing the page layouts?

I am working on an excel workbook for a program at our school. I need to set page breaks between different sets of data in my workbook. I manually set all my horizontal page breaks fine, but my vertical page breaks are giving me trouble. The program imposed a vertical page break on my workbook that cuts off 2 columns and puts them on a separate page, but there is plenty of room on the page I want them to be on (at least 1/2 of the page is blank). I have tried to move and delete this page break, but neither works. Any suggestions?

I want to have one master page in a workbook & have it update multiple pages if I make changes to the master page. How can I do that?

My purpose is that I use the page over & over in separate worksheets but hide different columns to print reports for various reasons but I only want to update one page.

I have an Excel 2010 file with many worksheets--some of which are one page in length and others which are multiple pages in length (each worksheet is only one page in width). I have used Page Layout --> Page Setup --> Header/Footer --> Custom Footer in order to create the same footer on each page with consecutive page numbers ("&[Page]").

When I select all worksheets and go to Print Preview (there should be 42 total pages), the first 40 pages print consecutively (1-40), as they should, but the final two worsheets in my file print with page numbers #1 and #2. In other words, my pages are numbered 1, 2, 3, ... 40, 1, 2.

All of the settings in the Customer Footer appear to be identical, so I don't understand why the last two pages are not printing as 41 and 42. Any idea what might causing this?

Thank you!!

I have a workbook (happy to e-mail it if needed) that prints 8 different worksheets with the click of one button. I have recently installed a new printer and would like to make one of the worksheets print to a different print tray. Is this possible? My thoughts were that it may be possible through a page setup. Thanks in advance!

Hi there.

I have a bit of a question that I am hoping someone can provide an answer to...

I currently have an excel macro in place that I use to print off a monthly report. However that report consists of multiple Worksheets and Workbooks. It prints say 5 worksheets from workbook [a] and then 2 worksheets from workbook [b] and then a few more from workbook [a] and so on. In total it has 49 sheets that must be printed.

I have now been asked to apply page numbers to the report.

I have tried the basic way: print it all out, then put it back in the printer and double print the page numbers on it. However, simple in theory, but not when all the paper gets munched up by the printer. DOH!

So I'm wondering if there is some code I could put into my macro that will get it to print a page number onto the page and a consectutive page number on the next printout.

If anyone has a solution to this, I would be VERY grateful!

Our time sheets are kept individually on separate worksheets. These are combined for the total company to create the workbook. This continues throughout the year.

Problem: I need to be able to print the workbook with each page showing its page number and the total pages of the workbook. Example: Page 5 of 120. Because worksheets are added throughout the year, the solution must allow for those changes without the need to count total worksheets.

Is this possible and how is it done?


Hi all,
Apologies if there is another thread on this topic, but a quick search did not provide any results.
Here is my problem:
I have a workbook with multiple worksheets Each of these worksheets have different sets of data and graphs on them I have set up automatic publish to web page on several worksheets when saving the workbook I created the workbook in Excel 2003 and everything was working fine After upgrading to Excel 2007 I have noticed that when saving, any worksheets that do not have focus will not publish any of the graphs on that page. NOTE: The data is published fine. Is this a known problem that there is an easy fix to?



I am trying to print multiple worksheets (specifically, a 19-worksheet workbook) and I want each worksheet to contain the next sequential page number (i.e. worksheet #7 will read "Page 7 of 19" in the right-hand footer; each worksheet is only one printed page).

The only way I know to do this is to input "Page &[Page] of 19" in the footer and change the "First Page Number" on each worksheet. I have tried this twice now (and saved after both times), but Excel does not keep the First Page Number that I assign to each worksheet.

For example, worksheet 7 reads "Page 1 of 19" because Excel has not retained the First Page Number of 7 that I set for that worksheet. I have tried a few times, but do not know why Excel is not saving this information. Any ideas?

I have a report that I need to number them consequitively:
worksheet 1 Page 1
Worksheet 2 Page 2
Worksheet 3 page 3 to page 4
Worksheet 6 page 5 to 7

I there a way to renumber worksheets within the workbook ? Thanks.

I am trying to create a button, that when I click on it, it prints out the worksheet in landscape (pageset is for portrait), all items in rows A-K. But doesn't print the first and last page, which are a cover sheet and terms and condition pages which I don't need to print on this copy. The worksheets vary in number of pages, so while the last page is Page 8 one time, it could be page 10 the next, the page 5 the next. So last page can vary.


Prints Landscape without changing the current print setup.
Prints everything in rows A-K
Doesn't print Page 1 and last page, whatever that page is.

Any help? I can't seem to find anything online to dictate, don't print first and last page.

Many thanks


Our time sheets are kept individually on separate worksheets. These are combined for the total company to create the workbook. This continues throughout the year.

Problem: I need to be able to print the workbook with each page showing its page number and the total pages of the workbook. Example: Page 5 of 120. Because worksheets are added throughout the year, the solution must allow for those changes without the need to count total worksheets.

Is this possible and how is it done?


Hopefully someone will find this easy to solve because it has been very difficult for me.

I have a spreadsheet with 3 macros that are designed to run on specific worksheets as follows:
Calibrate - should only run on worksheets beginning from SN to GD (28 worksheets)
CogCalibrate - should only run on 2 worksheets (WP & NS)
BIGCalibrate - should only run on 1 worksheet (BIG)

None of these macros should run on the remaining 5 worksheets (Contents, Instructions, DescriptiveStats, CalibrationSummary, Data).

To make it easier for other people to use this spreadsheet I have created a Contents page. How can I modify these macros to allow them to be run from buttons on the CONTENTS page? I have looked and tried various examples without success.

I have 1 workbook with 35 worksheets where each worksheet is &[page] of &[pages]. I want to select all worksheets and pdf but excel is giving me the total number of pages for all selected worksheets (e.g. worksheet one will have 4 pages but it will say "1 of 209" instead of "1 of 4"). Printing one worksheet at a time is trying, takes too long, and most of my workbooks have this issue. Is there any trick? I have limited VBA experience.

I have a workbook with multiple sheets. I want to be able to print the workbook by clicking on "entire workbook" and have the page numbering start with the second sheet of the workbook as page one. (The page numbers in my workbook are inserted by virtue of using the "insert page number" key under page footer.)

I know that I could accomplish this by keying in the actual numbers on the pages instead of using the "insert page number" key, but in my case some of the sheets contain multiple pages.

Any ideas?


I have a workbook that has several worksheets in it. Each worksheet is a table with multiple pages. Lets say I have 10 sheets, each with 5 pages. I would like to be able to print multiple sheets at once and have each table come out PAGE # of 5, instead of PAGE # of 50, like it would if I clicked on "print entire workbook". In addition, I would like to be able to select which worksheets I want to print. Can anyone help me with writing a VBA code so I can select the worksheets I want to print and then run the code that will print those sheets out with the correct page numbering?

Thank-you in advance for your time.