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

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 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 ...
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 PrintOu ...
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 ...

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,

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

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.

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...

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 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 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?


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?


I do a lot of forms and contracts for my organization, and many times these will run more than one page. My problem is ensuring that they will print properly, with page breaks falling where they should, not knowing what type of printers my many users may be trying to output these on.

I've found that using the "Fit To" command in Page Setup - Scaling will work perfectly for a one page document, setting the output percentage as necessary for different conditions. But for multiple pages, let's say setting it for 1 page wide by 3 pages tall, I can no longer ensure where the page break will fall. I'm hoping there's something I'm missing that might fix this, because after all these years of development, I can't believe Excel doesn't handle this better.

Let's say I have that 1 page wide by 3 pages tall example. My normal routine would be to set the print area first, then go to Page Setup and set the 1 pages wide by 3 pages tall, then change the View to Page Break Preview. If I adjust any page break in that mode, and then go back to the Page Setup commands, the setting in the Scaling section have reverted to a fixed percentage. It seems that Excel will not scale for different printer characteristics and allow variable page breaks at the same time. Scaling and page breaks are linked.

Is this right? Is there no option? It seems awful limiting if this is the case.

One solution might be to set up a macro that defines each page and prints it separately. But I run the risk of having different percentage outputs if I do that, and if someone wants to print to a PDF it creates multiple files instead of a single one with multiple pages.

There's gotta be a better way, maybe someone has a good idea. Thanks in advance for any help.


I have a sheet in a workbook that may just be called cover and other times front cover, feb cover etc. I use VBA to find the sheet and insert a new one infront of it. Is there a way to look for the sheet by specifying a wildcard or text plus the word cover


My request could be a little too vague for someone to give an answer at this time, but i am trying to determine a solution as follows

I have one long worksheet which will print over about 8 pages. The content of the worksheet is an accumulation of copies from other sheets. i use the xlend command to find the last entry, move down 3 lines, add a page break, and then do the next copy. The first page is a cover sheet, the second page a contents list, and pages 3 onwards is the copied data which will vary in length.

Ideally, i would like to add the page number starting on the 3rd page, but with a text of page 1, fourth page to be page 2 etc. If i could have it in the footer section it would ensure that it was aways at the bottom of the page in the same place.

Hope i have explained it sifficiently . Is my request possible to do.

I have a Book with 29 Sheets, 25 of those are identical tickets, but with a unique ascending numerical name. I need the tab name to be the same as the number on each sheet. The first number is listed on the first "Cover" Sheet and there is a summary of all tickets at the back of the book. I have found many different ways to do this, but none seem to fit just right. My problem is i want to be able to just input the starting number in the Cover page and all the other numbers to automatically populate including the sheet names. Also i dont want any sheet to be doing this everytime there is a change to a sheet in the book as the sheets have alot of caculations going on already and i dont want to unnessisarily be runiing scripts as it start to slow excel down. All Sheets will be constantly changing except for the cover page. I attached a simplified example, minus any coding for naming sheets Hopefully someone can point me in the right direction

I'm trying to print two sheets within the same workbook at the same time as a single print job. This way sheet1 is the front and sheet2 the back.

It I do select both sheets and then print active sheets Excel creates two print jobs, forcing the printer to start the second sheet on a new page.

This page break between the prints means that I have two single sheets printed out of the printer rather than one piece of paper in duplex.

Any ideas on a work around for this?

The two two sheets are only a sinlge page in size, but have different column widths so wont easily go onto a single worksheet.

Ive searched for this on the forum and there are numerous topics. HOwever i dont think they quite answer my query...

I have two Charts as separate worksheets.
I select both sheets and go to print - setup and select the multiple page option my printer has - 2 to 1.

I want both charts to appear on one single sheet.

I have done this before in the past but on a different pc and printer, with success.

Anyway, when i print, it prints the one page in portrait (correctly) with the first chart at the top of the page just as i would expect it, but then it leaves the second half of the page empty and continues to print the second selected sheet on a full page.

So i end up with both charts, one in half the size and one in full page size.

This is weird- never seen this before.

Tried it on another printer and it does the same, so im thinking it must be a setting somewhere, or im doing something wrong in excel.

I dont want to paste the charts as an object onto a separate worksheet as this would defeat my objective and take additional time.

Anyone else ever seen this?

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!

I've been struggling with this for a while. I think this is an issue with both Excel and my printer.

I have a workbook with 30 different worksheets that are all the same. There are 20 columns wide and set at a print area of 1 page by 1 page. Some of the worksheets print great, but for others I get a page that prints the second half of the columns (K through T).

When I email the document to another computer, it prints just fine.

Because I am getting some worksheets to print just fine, I have to think there is a setting in Excel that is throwing off the other worksheets, but that is just speculation.

Please help!

I have an Excel file with 102 worksheets named Properties, Cover, 00, 01, 02, 03 ... 99.
00 -> 99 are identical in format and their totals are wrapped up on the cover page.
I'd like to be able to check a radio button or enter a value on the cover page that will perform two separate tasks:
(1) I'd like to be able to hide rows on all workpages by making a selection or entering a value on the worksheet Properties.
(2) I'd like to be able to hide certain worksheets by making a selection or enter a value on the worksheet Properties.

For example on question one, on the Properties worksheet, I have identified rows 22, 23, and 24 as rows across all of the worksheets that I'd like to hide.
I'd then like to be able to go back and unhide row 23.
The default value would be to display all rows.

As an example for part two, I'd like to go to the Properties worksheet and unhide 01, 03, 06, 08.
The default value would be to have all numbered rows except for 00 hidden.

BTW this is a pain because I get a huge file and may only use a few of the worksheets. If there was a way to start out with only 00 and enter in the two digit numbers for the other pages that I would need and have excel automatically add those pages and have the worksheet cover automatically grab the totals from the new sheets that may be a better use of memory. At this point I'll take what I can get!

Thanks so much! This site rocks! It has solved many of the problems that I've been having but after searching on this topic, I could find anything that answered this particular question.


hi there..looking for guidance on how to get totals from different worksheets calculated on to the cover sheet.


cover sheet:
Day Shift Night Shift Total All Shifts
Form filled:

Cover sheet:

worksheet 1 (Day Shift):

Date: 3/5/10 3/6/10 3/7/10 3/8/10

Form filled: Yes N/A No Yes

Cover sheet: No No No Yes

worksheet 2 (Night Shift):

Date: 3/5/10 3/6/10 3/7/10 3/8/10

Form filled: No Yes No Yes

Cover sheet: Yes No N/A Yes

please tell me that this makes sense to someone and that you can help me! i am very new to excel so go easy on me. thanks in advance.

I have forms I need to print, there are 21 pages, when I use the "Record Macro" on the Developer menu the block the range I need to print, press print, Set printer options ie

Select "Printer",
Settings = Print selected range, Print Landscape, Fit to Page.

The Print.

I then close the Record Macro. and try to run it.

I get an error command.

The macro show as:

Please Login or Register  to view this content.

Where the last line shows as the error.

Also, every time I block a range to print, press print key, the printer option set the entire worksheet to print, not just the part I selected.

With large worksheets this take several minutes, I've waited as much as 12 minute for the preview page to show before I can manually change the options.

I am attaching the file I need to print. This is all old data. I printed it manually one at a time.

Please note I need to print all the even pages first then turn the paper over to print the odd pages so it page 1 and 2 are on opposite sided of one page

Any help will be appreciated

I have 20-30 sheets in a workbook. I am trying to select sheets based on checkbox criteria (true, false) and bring up printer menu (ctrl+P). So I can print out those selected sheets on a pdf file at once, instead of creating single pdf file for each sheet and combining them after. I can do this manually.
Please see below. q3 is linked to a check box, which changes true and false by clicking. Also, each check box represents two sheets.
If Sheets("COVER").Range("q3").Value = "True" Then
Sheets(Array("overall new", "overall be back")).Select
I have 12 checkboxes and all located in "cover" sheet. I do not how to select sheets in addition to already selected sheets.

Thanks in advance. Please let me know if need more explanation.

I have spreadsheet with various sheets that print as a book.
So sheet1 will be the Cover, sheet2 the Index, sheet3 the first report, etc.
Each sheet is not 1 page. The page numbers have to follow on each other.
So I've found a macro to calculate how many pages there are per sheet.


        'i is the row numbers where the sheet names are stored.

        For i = 45 To 64
        MySheet = Worksheets("Settings").Cells(i, 3).Value
        ActiveWindow.View = xlPageBreakPreview

        Worksheets(MySheet).DisplayAutomaticPageBreaks = True
        HorizBreaks = Worksheets(MySheet).HPageBreaks.Count
        HPages = HorizBreaks + 1
        VertBreaks = Worksheets(MySheet).VPageBreaks.Count
        VPages = VertBreaks + 1
        NumPages = HPages * VPages
        Worksheets(MySheet).DisplayAutomaticPageBreaks = False

        Worksheets("Settings").Cells(i, 4) = NumPages
    Next i

This code then insert the number of pages on the Settings sheet, where I can calculate the first page number of each sheet, example Sheet4 is page number 12.

I want to know if there is a way to read from the Settings sheet these numbers and insert them in this Print dialogue box per sheet... in the First Page Number block which is currently on Auto.

Uploaded with

I need the correct page numbers to show, so if I only print out Report 2 which starts on page 5 and is 5 pages long, Page 5 needs to be at the bottom of the first page of Report 2 and Page 10 on the last page of Report 2.