Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Formatting All Sheets In A Workbook To Print The Same

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

I am having trouble getting all sheets in the workbook to print the same. I
have tried selecting all sheets and setting up my header and footer, margins,
etc. But, when I then go to print preview, only the first sheet in the
workbook has held the settings.

View Answers     

Similar Excel Tutorials

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 ...
Print all Embedded Charts in the Entire Workbook
This macro will print every embedded chart in the active workbook. If you have an embedded chart in any worksheet ...
List all Conditional Formatting Formulas in Excel
List all conditional formatting formulas in a worksheet in Excel. This allows you to quickly view and manage all of ...
Print Entire Workbook in Excel
This free excel macro allows you to print the entire workbook in Excel. You can easily set this macro to a button ...

Helpful Excel Macros

Print Preview Screen Display for The Entire Workbook in Excel
- This free Excel macro allows you to quickly and easily display the print preview window or mode for the entire Excel wor
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 Preview Display for Specific Worksheets in Excel
- This free Excel macro allows you to display the print preview mode or window in Excel for a specified Excel worksheet.
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

Similar Topics


Just a quick one regarding the print area. I have a worksheet and I have specified the are I want to print off via the 'Set Print Area'. I then go into the print preview and it defualts to, say. 50 sheets - at this point everything is fine! However, when I go to modify the print preview to, say, 1 page wide by 10 tall (which should be possible given the sze of what i want to print) it reverts to around 17,000 sheets and then the warning box saying 'Margins Do Not Fit page Size'!

I have tried copying the info to another workbook - doesn't work!

Anybody have any ideas?




I have a work book with 5 different sheets. I wanna print all the pages with the same print settings(Page setup settings) mentioned in the first sheet (For eg: Page setup, Header and footer mentioned in the first sheet should reflect in all the other sheets of the workbook). I already tried out by selecting all the sheets and applied the above procedure but not working out.

Anybody pls help me out.


I have a workbook that has a total of 44 tabs; tab 1 = 'Summary' and then 'T1' through 'T43'

When I go to print the workbook, I do what I always do with other workbooks: I select all sheets and hit Print. On this workbook, tabs 'T15' through 'T43' print first, and then 'Summary' through 'T13' print. Tab 'T14' does not print unless I print it separately from the rest.

I am not using any code to do this printing. I am right-clicking on a tab and selecting all sheets and then using the standard Excel toolbar or File...Print menus. When I view it in Print Preview with all sheets selected, T14 is there and looks fine. There are no extra page breaks that I can see.

Has anyone else experienced this or does anyone know what is causing this?

I would appreciate any help that can be offered.

I have created a workbook with 19 identically formatted worksheets in it. Each worksheet appears on-screen printing neatly on 3 sheets of paper as desired. This formatting appears consistently in all views while I am working on it and in the print preview. However, when I go to print the entire workbook, only the first worksheet keeps that formatting. Worksheets 2-19 get much larger and no longer print neatly onto 3 sheets of paper each. If I print each one individually, the formatting remains correct and everything is fine. I have checked my page breaks, my scaling percentage, my margins, that everything is centered appropriately, and have reset each one of these things on every worksheet and saved the settings. Likewise, the problem also occurs when printing a single worksheet with multiple pages. If only one copy is printed, the whole thing maintains its formatting. If more than one copy is printed, all of the formatting changes forcing it onto many more pages than appearing on-screen. Others in my office with Excel2010 are experiencing the same problem; nobody has been able to figure out a solution. Those using Excel 2007 can print the same documents with no problems at all. Is there a setting I am missing somewhere that needs to be changed?

I am trying to update footer in a workbook by updating a field on a single sheet. I have coded it two different ways and get two different problems.

Code snippet 1: The following works if I print each sheet individually, but if I print the entire workbook, only the first sheet's footer is updated. Also, if I select all of the tabs in the workbook and do a print preview, only the first worksheet's footer is updated

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.RightFooter = "Current Month: " & Format(Sheet1.Range("C6").Value, "mmmm yyyy")
End With
End Sub

Code Snippet 2: This seems to work. If I print the workbook, each of the sheets seems to update, however when I select all tabs and try to print preview, only the first sheet is displayed. I would like to have the ability to print preview all sheets before printing.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each sht In ThisWorkbook.Sheets
sht.PageSetup.RightFooter = "Current Month: " & Format (Sheet1.Range("C6").Value, "mmmm yyyy")
Next sht
End Sub

Thanks for the help ... cc

Hi all. I have been given a small task which will require me to use VB code within an excel workbook.

I have been given a workbook that contains say 10 sheets. The first sheet is a summary sheet that will contain a 'Print' button. The idea is that when the Print button is selected all the sheets that's have been filled out will be printed.

For example.

I have a workbook that has 10 sheets to fill out. The user has filled out 7 out of the 10 sheets and when selecting the Print button, I require only the 7 completed sheets to print.

I thought the best way to go about this was to look at 1 particular cell on each sheet. If the cell is blank, do not print the sheet, otherwise do so.

I'm no VB coder by a long shot, although with a little help I can normally manage to make this kind of thing.

Any help this this would be most appreciated.

Thanks is advance for any help with this.

Jon Keane

I would like to use a userfrom to choose which of 4 sheets I would like to print from my workbook. There are many examples out there of populating a dialog box with all the sheets in a workbook and checking the ones you want to print, but my workbook has many sheets and I only want to use 4 of them. Are there any resources out there that teach you how to create a userform with a specific list of sheets to print from . I would also like a text box next to them that someone can enter the number of copies they need.


I am a continuing Computer support specialist and I am having a very rough time with excel and their print/print preview events. I am required to set up a User Form with a multi-select lstbox and two cmd buttons one for printing and one for exit printing. When a user clicks on the icon in the file menu for print or print preview the code for the BeforePrint event is fired. This action loads the lstbox with all sheets in the Workbook and then displays the User Form. The user can then select which sheets to print out or preview. One major problem I am having with it is the fact that the PrintOut method triggers the before print event. One more thing, when the user selects sheets and clicks print sheets cmd btn I am to Print the selected sheets and set their footers to match the sheetnames. Please help if you are able to. And Thank you very much in advance!!

Is there a good way to, if you will, "export"/"import" just the 'page setup' settings, like headings/margins/etc. so that it could be easily copied to another worksheet or workbook?

I've got a workbook with about 5 sheets that I need to have two print variants as portrait on one page, and the other as landscape on two pages...when I go to print these, I have to keep changing it back and forth and on occasion, I will miss something, and get print wrong...and using a $.25 color page print at library, I don't want to make too many errors.

I'm on Excel 2000 vers so anything available in newer vers is out.



I need some help with print / print preview. I have 2 seperate print buttons setup in my application on 1 sheet. I have a data area that is set to print / print preview. I use the following code to put a special date in the footer. On the same page I also have a chart that is setup to print / print preview. I need to set the footer again to this special date - I'm assuming the chart object needs to be referenced instead of the workbook ...

Any help would be greatly appreciated.

Code is this :

Sub Saved_Date_Footer()

Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.LeftFooter = Range("date_capture").Value

End Sub



I would like to add a little something to all the help everyone has
offered on the formatting issue in Excel. If you would like to format
all sheets including (Header, Footer, etc..) you need to make sure and
use the "page setup" under "file" menu, because using the formatting
from the print preview option will only make changes to the sheet you
are currently viewing.

I'm guessing but I think MS wanted to make sure you don't accidentally
format all sheets thru print preview so they make you choose it from
the file menu. It is the same thing as the repeat columns and rows,
which are greyed out under the print preview option, but not when you
choose "page setup" under "File"

Make sense?

Hope this helps.

On Excel 2011 I have a sheet that shows blank in preview and print (to pdf or printer) with only the header and footer. I have to copy and paste this results sheet to a new worksheet in order to print or preview it.

Eliminating the print range and re-setting it doesn't fix it. can't even just select a smaller part of it and preview it.

Hi this problem is really getting me down and frustrating me to death.

I have multiple sheets in a workbook and have [and continue to set up] print paremeters for each sheet in the workbook.

However, if I move onto another tab, and then return to a previously setup tab, all of the print settings have been lost again, even "Custom Footer" infomation, e.g. even my free text "disclaimer" in the footer has reset to old text?

I would appreciate any help on this one

many thanks


hi everybody

does anybody know how to create a code that:
selects all data (select range that contains data) on the spreadsheet then does print preview and reduces all border margins to minimum desirable range (.04 header & footer, 0.7 top & bottom and 0.5 left and right)?

i tried to record a macro directly from excel (without actually writing a code) but when it's run it gets stuck at print preview screen and doesn't close the screen. also margins seem to be being left at higher range than above.

any help would be greately appreciated



I have searched everywhere for the answer to this, and I have yet to really find any solution, please help!

I am trying to add a picture to the left header for about 10 sheets in a workbook to make them all look consistent in size. (I previously have embedded the picture into the sheet, but if I change the print area, the logo size gets bigger or smaller and does not look consistent across all of the sheets.)

So I got to View->Header/Footer->Custom Header and I click the icon to insert the picture. After I browse for it and insert it (have tried several different formats) it shows up as &[Picture] as expected.

However, once I hit OK or Print Preview it does not show up (occasionally it does, but then disappears shortly thereafter. I have adjusted the margins and tried everything else I can think of.

I have researched this and seen that other people have the same problem, but it does not seem like anyone has come to a solution.

Thanks very much in advance for any insight you can provide...



I have an excel workbook with about 7 sheets. On the top of each sheet there is a macro bottom "print":

Sub Macro15()
' Macro15 Macro
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,3,,,TRUE,,FALSE)"
End Sub

All of the sheets print on 1 page except for 1 sheet which prints on 3. So when your press print, 10 pages actually print. When I print it, I would like it to say 1 of 10, 2 of 10 ect. Or 1 of 7, 2 of 7, 3a of 7, 3b of 7, 3c of 7 (for the sheet with multiple pages).

When I try to add this in the footer, it only does the # of pages for that specific sheet (1 of 1 or 1 of 3).

Can I somehow fix this? Maybe add it to the macro?


I have a workbook with 4 spreadsheets, each taking 1 page to print.

I want Sheet 2 to print on the reverse side of Sheet 1 and Sheet 4 on the reverse of Sheet 3 so that all four pages of data takes 2 sheets of paper to print.

I have tried selecting 2 sheets at a time and 4 sheets at a time but printing result is always single sided even though "duplex" is selected in printer properties. In the Print dialog box I have tried selecting "Active Sheets", "Entire Workbook" and even "Selection". I've also tried specifying pages 1-4 rather than All. Eventually, by printing to a PDF creator, I have concluded that Excel is treating each tab is a separate print job. i.e. I get 4 separate PDFs.

I'm sure I've done this successfully before but I can't remember how.

I'm using userforms as a method to input data and create forms. So far I have Hidden Excel and opened the main Userform on open. I have a total of 5 userforms that all create different forms that I need available for print. On one of the userforms I have a command button to print preview the appropriate worksheets. I need help with the code to show the sheets, execute the preview/print, hide the sheets. All of this without ever displaying the workbook to the user if possible. my current Print preview command is

Please Login or Register  to view this content.

Hi All,
In my everyday work, I need to check the header and footer of every worksheet in an excel workbook and then edit them. In case of big workbooks having many worksheets, this becomes a tedious process. Does someone know of a macro that can do either of the following:
1. Print all the header and footer from all the worksheets in a workbook as a textfile (log file).
2. Detect the sheets that have the header and footer and ignore the rest so that I can run a macro on these selected sheets to delete the header and footer (I already know of one such macro).

Thanks in advance,

Hi All,
I am creating a macro to format the margins and headers and footers so that it is print ready for the user.
The macro works fine, although when it has finished its formatting it stays in the print preview screen. I want it to go back to the workbook.

Can this be done?


I am trying to print my document, and I select the content.
I go to the File, and select "Print Area", then I select "set print area".
I go into the print preview, and the selected pages are at 21 %. I adjust
it back to 100 %, then select ok. I look at the document, and the same
page is repeated in the print preview. I go back through the process, of
selecting print area, setting the print area, and print preview, and the
pages are at 21 %.
I have tried right clicking, and resetting all page breaks, clearing the print
area. I have also closed the document with saving changes, and I have
tried turning off my computer and restarting it. I have used excel for
and this is the first time this has happened.

Good afternnon all,

I am trying to print a simple 36 line spread sheet that ends in column T. I am trying to print on legal paper. I have tried to manipulate the margins, reduced the scaling etc..

I am able to see the complete document in the print preview. It is filling the page from left to right (looks ok to print). When I goto print, it prints very small and there is still approximately 3" of blanket space (that should be used) .

I have tried multiple times to modify any settings..Any help is appreciated.


My workbook has several rows at the top which I want to use as a header for the first few pages, but not the last. So I've used some VBA code in the beforeprint event to cancel the print job, print the first few pages of the workbook, hide the unwanted rows and then print the last page.

It worked fine in Excel 2003 but in Excel 2007 the print preview command bar is not displayed so the user has no way of closing the print preview except closing the application from the taskbar.

Is there a way of displaying the print preview commandbar in Excel 2007?


I', trying to number sheets in a workbook (not pages in a sheet). Say I've got a number of sheets.
I've tried this: I select all sheets. On the first sheet I go to Header/Footer & Customize Header and click the page Icon, which puts &[Page] in the header. I go to print preview and I see the number 1. Fine. Then I go to the next sheet and it also has 1 for the page number. I wanted to see 2 printed.

thanks for any help

I am using Excel 2007 and Windows Xp Pro.
I am going crazy trying to figure out why my page numbers don't print on my color Epson R800. They do print on my black and white printer though.
The numbers show up in Preview mode but will not print. I even tryed an empty page - still won't print
I am competely lost in this and need the page numbers on the project that I am doing.
I tried to have the page numbers on the header and that seems to work, but no way can I get them to print on the footer.
Any help would be greatly appreciated
Thank you