Email:      Pass:    Pass?
Subscribe for Free Excel tips & more!
E-mail:
Advertisements


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?

Thanks!!


Similar Excel Video Tutorials

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

So:

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

Mike

Hi,

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

=IF(Cover!S4="Jan",1,IF(Cover!S4="Feb",1,IF(Cover!S4="Mar",1,IF(Cover!S4="Apr",2,IF(Cover!S4="May",2,IF(Cover!S4="Jun",2,IF(Cover!S4="Jul",3,IF(Cover!S4="Aug",3,IF(Cover!S4="Sep",3,IF(Cover!S4="Oct",4,IF(Cover!S4="Nov",4,IF(Cover!S4="Dec",4))))))))))))


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

MACRO....;

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


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


Hey guys and gals,

I'm looking to have my excel sheet automatically move the page breaks around depending on how the data works out. Currently I have a sheet that when a product is selected, it is added to a cover sheet. Then as more products are added, the cover sheet will expand as products are shown and if a product is removed, the cover sheet will shrink so that it isn't always two pages or more long.

The problem I have is that after a certain point, Excel will then cut off at whatever row it can fit on one page, and continue on the next. I'd like to have it so that if it cannot fit whole groups onto the page, it moves the whole section to the next page on print. Here is an excel document to show what I am hoping to do, and if you know the fix please let me know!

http://www.2shared.com/file/ooyxP-cQ/Test_Data.html

Thanks!


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
Range("A2").Select
Range("C8").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "1"
Range("C9").Select
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?
thanks,
-Justin


I use a Check Box method to print different sheets within same Workbook. I have a total of 18 Check boxes each check box represents a different sheet that can be selected, but I noticed if one sheet has a lot more data or photos than another it takes a longer to send to printer and in turn causes it to lag behind, at which it causes it to fall out of order. Is there a way to keep it in numeral order or ?. I tried applying
Code:

     Application.Wait Now + TimeSerial(0, 0, 5)


to slow it down some, but this can sometimes take quite a long time and not always work.

As it stands right now, when I check the desired sheets using a check box, then select print command button my selected sheets are sent to my default printer, which is PDF Creator. From there it loads all the files to be printed. I can re-organize the print order from there, but all the file names are the same, they are named after the workbook. Is there a way to either keep the print order in line or give it, its own print name instead of the workbook name. Here is a sample of the code I am using at the present moment
Code:

Worksheets("Cover Page").Visible = xlSheetVisible
 Worksheets("Cover Page").Select
     Range("A1:Q56").PrintOut Copies:=1, Collate:=True
     Worksheets("Cover Page").Visible = xlSheetHidden
     Application.Wait Now + TimeSerial(0, 0, 5)
    End If

If CheckBox2.Value = True Then
 Worksheets("Client Information").Visible = xlSheetVisible
 Worksheets("Client Information").Select
     Range("A1:AM50").PrintOut Copies:=1, Collate:=True
     Worksheets("Client Information").Visible = xlSheetHidden
     Application.Wait Now + TimeSerial(0, 0, 5)
    End If





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,

Dave


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?

Thanks.

Paul


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

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


Hello everyone.

I'm creating reports from big tables of stuff in excel using vba. Once they are made properly vba exports them to pdf. That all works fine. But I'm having trouble inserting a cover page the way I want.

When the data has all been formatted etc etc. it's all shifted down and the cover page (just a few titles and a picture) is pasted into the new space above. Then vba throws in a page break under the cover page and it's all exported to pdf.

The problem is that the data can be any number of columns wide, and is fit to the page width using:

Code:

With Report.PageSetup
       .PrintTitleRows = "$1:$2"
       .Zoom = False
       .FitToPagesWide = 1
       .FitToPagesTall = False
End With


With "Report" being set as the worksheet that will be the final report (i.e. it includes the cover and data).

When the columns are fit to the width the cover page gets the same treatment (since it's on the same worksheet). The result is that the cover page in the pdf is tiny, like you're zoomed out.

So what I want is some sort of "static" cover page. It should always look identical no matter how many columns there is.

Would really appreciate any help.

P.S.

I thought maybe if I could somehow export two sheets to pdf that might do it, but I couldn't get it to work. Maybe if I could somehow export a worksheet "Cover" and the data worksheet separately the cover wouldn't get fit to the width. Does anyone know how to do this?

I want to avoid using shapes if possible also, they're messy.

Sorry, can't show the files because they're confidential. I can post any code that's relevant, but I don't think there is much else relevant to this problem.

For reference, the pdfs are created with:

Code:

Sub createPdf(byval name As String)

    Dim exportRange As Range
    
    Set exportRange = Report.Range(Cells(1, 1), Cells(rowCount, columnCount))

    pdfName = Settings.Range("u6") & "\" & name & " " & Settings.Range("u7") & ".pdf"
    exportRange.ExportAsFixedFormat Type:=xlTypePDF, filename:=pdfName, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub


Where rowCount is the number of rows of data and columnCount the number of columns.


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

MB


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.

Code:

 
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
    Workbooks(ThisWorkbook.Name).Unprotect
    Sheets("Coupons").Visible = True
    Sheet17.PrintOut Copies:=1, collate:=True
    Sheets("Coupons").Visible = xlVeryHidden
    Workbooks(ThisWorkbook.Name).Protect
End If

If Workbooks("Reports to Go....xls").Worksheets("Office Information").chkcover = True Then
    Workbooks(ThisWorkbook.Name).Unprotect
    Sheets("Cover").Visible = True
    Sheet16.PrintOut Copies:=1, collate:=True
    Sheets("Cover").Visible = xlVeryHidden
   Workbooks(ThisWorkbook.Name).Protect
End If
    Thisworkbook.Sheets(aShtLst).PrintOut
    
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.

Dean


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

Code:

Application.Dialogs(xlDialogPrint).Show


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?


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?

Corey....





Hi,

I've got a workbook that i need to create a macro that copies cell values into another sheet on cell value update.

Firstly on the first sheet named "COVER PAGE" i have a table from S24:W65 that contains data that is only written there from an import macro from visio (data is never written there manually).

From E24:E65 on COVER PAGE, i have the names that each of those values corrospond to (each name has 5 values, columns S thru W)

On the second page of the workbook called "RESULTS" i have a table from J5:S48 but only columns K,M,O,Q and S are data entry columns.

I use the following formula in cell K5 to lookup E24:E65 for a cartain name contained in column J, and return the first of the five data columns. I have similiar formulas in columns M, O, Q and S but instead of returning column 15, i return 16-19.

I need to do all this with code but my VB skills are a little rusty. Could anybody give me a hand?

=IF(ISERROR(VLOOKUP($J5,'COVER PAGE'!$E$24:$W$65,15,0)),"",VLOOKUP($J5,'COVER PAGE'!$E$24:$W$65,15,0))


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?

Corey....





Hi all

I've created a front cover page for a document I'm putting together.

The cover was the last thing I inserted and as I resized the margins on the first page it somehow screwed up only the margin for the header on page 2.

If I can figure out how to realign the header on page 2 then I'm good to go, but I'm stuck!

Anyone have any ideas?

Many thanks.