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

Printing Cover Pages

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

The printer I'm using has a setting that prints a cover page for each document.

For an Excel workbook I inherited from another user, it prints a cover page for EVERY worksheet that I print, even if I select multiple worksheets w/ the SHIFT or CTRL buttons. The output is:

Cover Page
Sheet 1
Cover Page (different "job #" listed)
Sheet 2
Cover Page
Sheet 3

I initially thought it a problem with the printer settings. However, with a brand new Excel workbook, I can select multiple worksheets and print with the following output:

Cover Page
Sheet 1
Sheet 2
Sheet 3

The fact that only one cover page can be done on other workbooks leads me to believe that there may be a setting in the bad workbook that causes multiple sheets to be printed with individual cover pages.

I've tested with macro's and individual "click and print" methods and this specific workbook prints out cover pages while my other tests only print out 1 cover page.

Does anyone know how/why this could be happening? Is there some sort of system setting to send print jobs individually or as a group to the printer?


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 ...
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 ...
Prevent Charts from Printing in Excel
This is how you prevent a chart from appearing when you print from Excel. This is a great feature when you want to ...
Performing Calculations in Excel - Order of Operations Explained
In this tutorial I am going to cover how to perform calculations in Excel. Calculations in Excel follow the same ba ...

Helpful Excel Macros

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
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
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 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 within
Print All Charts That are in a Worksheet
- This macro will print all of the embedded charts which are on the current active worksheet. Each chart will be printed o

Similar Topics

I have a 4 page excel document that I want to print out
on 11X17 paper front and back so that it is similar to
a manual. The layout I desire is page 1 is the cover, 2
inside the cover, 3 opposite 2, and 4 on the rear
cover. I am having a problem.....the printer is an HP
laserjet 5000N with a duplexer. I can't get two pages to
display on one side of the 11x17 sheet. [/img]

I have a workbook with 20 sheets. Each sheet has been formatted with a print area to print a single page for that sheet. If I select entire workbook and view the job, there are, as expected, 20 pages. When the job is printed, I end up with 2 entries in my print 1 and the other 19 pages. BTW, the first sheet is very sparse (like a cover page).

If I delete the first sheet and print the entire workbook, it creates a single print queue entry for all 19 pages.

The reason that this is an issue is that when this (original) document is printed to a usb Lexmark printer, the sngle page job causes the printer to hang. It gets real messy from there, ultimately requiring a reboot.

If this same document is printed to a usb Canon printer, it still creates 2 print queue entries, but it all prints just fine.

Has anyone else experienced this problem and/or know what to do?

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



Does anyone know how i can write this formula so it will work?


At the moment it does not work as I have too many IF's as I am sure you are only aloud 7?

Please help

Hi all, i'm pretty new to Excell in the workplace and would really love it if you lot could help me with this difficult (TO ME) macro i'm struggling with.

I'm trying to finish off this macro, or mod it so that i get the page number, tab number, a cell reference in the respective headers and footers as explained in the macro below. only thing is i don't really want the tab label, cell reference (rev number/date) on the first page ie the first tab called coversheet.

The text in bold italics is my attempt at this but i'm stuck at it.

Please guys i'd appreciate the help, who knows i might actaully get good at this one day!

Cheers, Tom


Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim WS As Worksheet
For Each WS In Worksheets
'REV number and date in RHS footer on all except cover sheet
If Not WS.Name = "Cover sheet" Then
WS.PageSetup.RightFooter = Worksheets("Cover sheet").Range("B55").Value & _
Worksheets("Cover sheet").Range("L35").Value & _
Worksheets("Cover sheet").Range("B56").Value & _
Worksheets("Cover sheet").Range("L35").Value & _
Worksheets("Cover sheet").Range("A55").Value & _
Worksheets("Cover sheet").Range("L35").Value & _
Worksheets("Cover sheet").Range("A56").Value
'Document number in LHS header size 14
WS.PageSetup.LeftHeader = Worksheets("Cover sheet").Range("A10").Value
'Tab name in RHS Header BOLD fontsize 20
WS.PageSetup.RightHeader = "&B&25&A"
'Page number in Central footer BOLD size 14
WS.PageSetup.CenterFooter = "&B&14&P"

End If
Next WS
End Sub

Hi All,

Would it be possible to create a Macro that would enable me to, with a single click, send a print copy to the printer with the print area assigned to the last non-blank row?

The maximum print range that I have now is between B1 and N208. Although the number of colums won't change, the rows will. The table I am creating, is for people that will most certainly not know the difference between "update print area" and "print", and eventually, they will print without updating the print area resulting in 3 trees less in the world. Further, I have another sheet that will be the cover page for this printout. Could it be possible to have it printed automatically when printing the former?

So far, I have a code for simple printing that I created with record Macro:

Please Login or Register  to view this content.

Would it be possible to modify the code to include the resetting of the print area and the cover page?

Thanks to all

Hey guys,
I am trying to create a production report that has a cover, and then many pages that the cover references. I want to be able to either fill in the corrisponding cell on the cover and the correct page gets named. Then I want a cell in the page to name itself after the Page. For example the cover would get typed to look like..

Job 1
Job 2
Job 3

Then the tabs would automatically get named Job 1 Job 2 Job 3

I'm not sure if its possible, but any help is appreciated.

p.s. Naming tabs and the cell fills in correctly works also, either way

Hi, I have a workbook with like 15 sheets. The first sheet is called "Cover" with the rest called "Sheet 1" "Sheet 2" etc on to "Sheet 14". I want to added page numbers so that "Sheet 1" is labeled "Page 2 of 15 (May 2009)" and so on. I do not want a page number to be displayed at the bottom of page 1 which is the cover page but I do want it to count as page 1. My main problem is after I add in page numbers and select the sheets "Cover" to "Sheet 14" to convert to a pdf, once converted all the page numbers are wrong. "Sheet 2" will read something like "Page 1 of 1 (May 2009)...."Sheet 3" will read "Page 1 of 1 (May 2009) and so on

Thanks for any help.

is there a way to print out every tab EXCEPT 2?

I've got a button that does this:

Private Sub CommandButton1_Click()
ActiveSheet.PrintOut Copies:=1, Collate:=True
ActiveCell.FormulaR1C1 = "1"
End Sub

Every time our printers cycle, it puts a cover sheet on whatever is printed out. Its annoying, and I'm trying to print everything at once.

That macro prints out Sheet1 with a cover sheet, Sheet2 with a coversheet, then every other sheet I want with just 1 cover sheet. I don't want Sheets 1 or 2 print out. Is this possible?

Hi, I'm hoping someone can help me with a vba printing question.

I've got a macro set up that cycles through all the values that appear in a pulldown on a pivot table. For each one it does some formatting, and then calls ActiveWindow.SelectedSheets.PrintOut to print the page.

My problem is that the printer setup at my office generates a cover sheet with your user id for every job that is sent to the printer. So when I fire off 40 pages using 40 different calls to ActiveWindow.SelectedSheets.PrintOut , I get 80 pages. The 40 I want, and 40 cover sheets.

Is there any easy way to cause the 40 generated pages to be submitted as a single job? Maybe printing to file or something?

I've thought about having the section that cycles through the values in the pulldown copy the data into a new sheet and then once all of the pages are put there, print that, but I'm hoping there's an easier way. Is it possible to use "print to file" and just append the data in the generated files together?

Any other ideas?

Thanks for your help,


I am doing an excel workbook with multiple pages. On my cover page (the first sheet in the workbook) I have Item numbers - starting at row A3 0201-0001 row A4 0201-0002 and so forth. I made sheets for each individual item number. Can someone tell me how to insert a recurring cell - (B11) - from each sheet without clicking on each sheet. I put the formula in row A3 of my cover page which is: ='#201-0001 '!B11. This works fine but I want to drag the formula down from A3 - A30 and it won't recognize my other sheets. Can anyone help me?



I wrote the following code to add a button on each sheet except the Cover Sheet which takes you back to Cover Sheet when clicked. When executed, it creates all the buttons only on the Cover Sheeet and doesn't seem to link. Please help fix so that it create a button on each sheet, except the "Cover Sheet", and the button should link you back to the "Cover Sheet" when clicked.

Sub backtocoverbtn()
    Dim ws As Worksheet
    Dim MyCSBtn
        For Each ws In ActiveWorkbook.Worksheets
          If ws.Name  "Cover Sheet" Then
           Set MyCSBtn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=150, Top:=25, Height:=40, Width:=150)
            With MyCSBtn
                .Name = "MyButton" '& Trim(Str(i))
                .Object.FontSize = 12
                .Object.FontBold = True
                .Object.FontItalic = True
                .Object.Caption = "Back to Cover Sheet"
                .Object.ForeColor = &HFFFFFF
                .Object.BackColor = Sheets("Cover Sheet").Tab.Color
            End With
            Sheets("Cover Sheet").Select
            End If
End Sub

[Added code tags~VP]

I have a workbook with 2 tabs that I want to print out as one job. However, no matter how I've tried up till now, it prints out as 2 seperate jobs. This becomes an issue because for every new job, the printer puts a cover sheet with the User's Name on it.

Is there any way through code to make this work?

thanks a lot.

Dear all,

New to the forum and looking forward to post some questions I have and hope you guys can help me out!

My first question is regarding print areas in excel: is there a way to put the setting in a way that in an active sheet I would be able to skip a page. For example when my print area is set to cover 20 pages going down in two columns: could I make excel skip page 4 and continue printing with page 5?

Many thanks for your answer


I am having some difficulty finding a solution to my problem. I need a macro that will take multiple worksheets and print them out in a specific order.

Here is what I have so far based on my search, although I can't even get this code to run without an error. I have no idea if I am barking up the wrong tree or not.


Sub PrtCoverCoup()

Application.ScreenUpdating = False
Dim aShtLst As Variant
aShtLst = Array("Cover", FirstVisibleSheet, "Coupons")

If Workbooks("Reports to Go....xls").Worksheets("Office Information").chkcoupon = True Then
    Sheets("Coupons").Visible = True
    Sheet17.PrintOut Copies:=1, collate:=True
    Sheets("Coupons").Visible = xlVeryHidden
End If

If Workbooks("Reports to Go....xls").Worksheets("Office Information").chkcover = True Then
    Sheets("Cover").Visible = True
    Sheet16.PrintOut Copies:=1, collate:=True
    Sheets("Cover").Visible = xlVeryHidden
End If
End Sub

I only want "Cover" and/or "Coupons" to print if their associated checkboxes are checked. Being new to arrays, I have no idea if the code I have eliminates them or not.

You may have seen a similar request in a different thread. That was me. I am never sure if I am not getting a response b/c folks don't understand what I am asking or if no one knows the answer.

Bottom line, my problem is that when I use a single macro to send multiple print jobs at once to the printer the pages come out out of order depending on whether or not the printer prints them out face up of face down. I am hoping that by sending the sheets to the printer as one print job, they will print in the order I want.

Any thoughts, please...

Looking for some help please with relation to changing a sheet reference in a fomula using the content in another cell.
For example:
I have 50 identically formatted sheets numbered 1,2,3.... 50 in a workbook.
I then have a cover page which summarises certain date from all of the sheets on one page.
Each line of the cover page sheet represents one of the 50 sheets.

Therefore imagine that on the coverpage sheet I have a first column called sheet number and have the numbers 1 to 50 down the page.
Then across the page I have a number of columns which have formulas in them related to the data drawn from a specific sheet. Each row on the cover sheet represents information for one sheet only.

I have created the first row with all the formulas I want and then copy down to the other fifty lines. How do I make each line reference the sheet number in the first column so as I don't have to go in to each formula and change manually?

I am sure their is an easy way, but I can't find it.
Help please.


Hi I have a report in excel (about 16 pages long) on 12 sheets. I need to have a footer that labels the page number like

Page 1 of 16 and so on

However I don't want the footer to appear on the 1st page (the cover page, but I do want the cover page to count as the 1st page in the report). How do I do this? Thanks.

I am attempting to select about 20 different worksheets and then print them collectively using a macro. When I attempt this with



I can change the printer settings to print in color, double sided, and with holes punched in it. When it prints it will print in multiple print jobs at the pinter so that the 1st page is correct, the 2nd through 15th page is printed seperately with none of the settings, then pages 16-20. I can tell due to the title page out network printer adds before every print job. What is happening? Does anybody have any suggestions on how to correct this?

Word/Excel 2013

I am trying to insert a Word report cover page into excel in my first worksheet, but cannot figure out how to do it except saving the word page as a jpg then inserting as a pic. I was wondering if there is an easier way. I am creating a report in excel with numerous sheets but want a cover page on the front to look more professional.

i m enclosing herewith my project file whic is almost complete with fourm guru & others memeber Specially Nilim Sir

Actually my project is made for msoffice 2003 we create a menu.. which is not running in msoffice 2007 ..

we make a menu pane which is display left side and it has 10 option
1)Home 2)Entry 3)Edit 4)Prarup-1 5)Prarup-1 6)Print 7)Softcopy 8)Referesh 9)Roadlist 10)Help

We want 2 macro for option 6 & 7

for Printing Macro

in this we want a user form (we had already made but not working properly .. we got this frm in a existing office programe so may line so i confuse to make as per my requirement. pls check & change as per ur easyway) where few option display choose to print & when we choose then print as per my requirement

1 Cover Page : print sheet cover page
2 Summery : print sheet Home
3 Roadlist : print sheet Roadlist
4 Prarup-1 : print sheet Prarup-1
5 Prarup-2 : print sheet Prarup-2

for Softcopy macro

in this we want a user form where few option display choose to Save a new workbooks as per my requirement

1 Cover Page : copy sheet cover page
2 Summery : copy sheet Home
3 Roadlist : copy sheet Roadlist
4 Prarup-1 : copy sheet Prarup-1
5 Prarup-2 : copy sheet Prarup-2

All above sheets copy in a new workbooks at same place or folder where project file run with "DTOI.XLS"




my file is more then 1 mb so i make zip for attach..

I have set the Print Area in my WorkSheet to cover the area I need, but the
1st page is in LANDSCAPE and the page following is PORTRAIT.

Is there a way I can have this set to print automatically setting the 1st
page and 2nd pages to the required style?


I'm a bit of a Newbie when it comes to Excel but I have a good grasp on the basic functions, layouts, and tools that Excel has to offer. There have been multiple issues I have managed to resolve simply by Googling them or asking various co-workers. But there is one that has trumped me and decided to seek the wisdom of this community

I am familiar with having a cover sheet that seeks specific cells from different worksheets and combines them into on cell to add a total from all those different tabbed worksheets, but in my case I'm wanting to take it further and actually have excel search through multiple work sheets for specific keywords and their "quantities" for example in the image below

on two different days Bolts with part numbers of AS223FH34 were received into stock. one being a qty of 100 and the other being a qty of 50. I was wondering if there was a way to specify on a cover page that when someone enters that part number and then adds the QTY that it could automatically update on a front cover sheet to reflect that we have 150 of that part. Then the next day another 20 bolts are received and the cover page would automatically update to 170, etc.... Essentially like a find function to when someone actually inputs the "quote" info then it would trigger the function to record the QTY. The qty is the only thing Im concerned about.

This formula would span across every month of the year for 2010 as you can see in the tab that workbook was only for Feburary.

I have set the Print Area in my WorkSheet to cover the area i need, but the
1st page is in LANDSCAPE and the page following is PORTRAIT.

Is there a way i can have this set to print automatically setting the 1st
page and 2nd pages to the required style?


Hi, I have a workbook with tabs "A" through "Z". What I would like to do is type in my cover sheet say the letter "C". Have the computer look through find the tab "C", then input a specific field on my cover sheet and then print what is now in tab "C". What I am having trouble with is finding a way to search through tabs. Can someone please help?

Thank you.

Here's the problem: I have a workbook with multiple sheets in it. Each sheet contains multiple pages when printed out. Also, each sheet has page number separate from the other sheets. For example: 'Sheet 1' has 5 pages, so it is number "Page 1 of 5", "Page 2 of 5", etc. Each additional sheet has its own unique page numbers.

When I print off multiple sheets at a time, I want each sheet to maintain its unique page numbering. The problem is though that when I either group specific sheets and select 'Print' or if I select 'Print Workbook' from the print options, the unique page numbering for each sheet disappears and all the documents printed are combined into one page numbering sequence.

Is there an option I'm not aware of that would solve this problem? If not, how could I write a macro that would solve this problem?