Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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 Only Specific Parts of a Worksheet in Excel
In Excel you can select parts of a worksheet to print while ignoring all of the other data on the worksheet. This a ...
Print Gridlines in Excel
I'll teach you how to show gridlines for Excel when you print your files. This is a neat little feature that will ...
Print Comments in Excel
How to print the comments in a worksheet when you print from Excel. This includes how to print the comments within ...
Change the Default Number of Worksheets Created in a New Excel Workbook
Learn how to change the number of worksheets that are in new Excel workbooks. Excel workbooks always have 3 worksh ...

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?



:Dear all,

I have a workbook mede up of 5 shees, the first three containing only text and numbers while in the 4th, 5th and 6th I have pasted some pictures, cut from a .pdf file.

Now, all the sheets and their content are displayed correctly in the print preview, margins are ok so are page breaks... point is the last 3 sheets, those containing pics are not printed, and the second page is always printed in colours.

I have deleted any print area and when I print I select the option 'entire workbook'.

Is there anything I have to take in account while printing a whole workbook? Are printing settings defined per sheet? Are there any specific setting for sheets containing images?

Thanks a lot in advance



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 have 2 linked workbooks. One that contains changeable information that will be used to pre-populate cells in the 2nd workbook, including fill header and footer. I created an insertheaderfooter macro within a module in the 2nd wb and then put it in an activate worksheet module and within the before print workbook module. If I use excels print this works. But I created my own print button that does other things as well and when I do this it does not change the header footer. If I do a print preview first, it will update it. But, if I don't if will just print whatever the linked cell had in it before the change.

Any ideas on how to fix this?

P.S I also put the insertheaderfooter in the print macro as well as within sheetchange modules. Each of these works, unitl I go back to the first workbook and make a change. If, I go back to the second workbook and immediately hit my print button, it does not update. If I make changes to the sheet it works.

I almost just want to forget about it, but you know how it is driving me crazy

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 an excel workbook where everytime the option of print entire workbook
from the print menu is selected, the last two sheets should not print, and
the total page numbers should get adjusted in the header accordignly aswell.

forexample: I have 14 sheets. only first 12 should print when the option of
print entire workbook is selected, and the page numbering should be 1 of 12,
2 of 12..............



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,

I have an Excel workbook with multiple worksheets (14). The Book was created in Excel 2003. Now it is being updated in Excel 2007. The sheets and associated charts display on screen in color but only the sheets that were created since moving to 2007 will preview and print in color. All the sheets that were created under the 2003 version of Excel preview and print in gray scale. The workbook is still being saved in a 2003 compatible version so that it can be shared with users who are not running the newer version.

We would like all sheets to print in color. What option, what setting haven't I found?

have a macro in Excel that works with the exception of one piece. That is, after Print Preview and selecting all the settings I need for the header and footer, I click the close button on Print Preview. When I run the macro, it does everything just fine except at the end. It leaves the print preview of the document on screen. What can I do to get the proper code in the macro so that the preview will close and therefore be back at the worksheet after the entire macro process?
Thank you

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?