Free Microsoft Excel 2013 Quick Reference

when printing multiple sheets, first prints single side rest doub.

Have just installed a double sided printer, and when I highlight several
sheets in an old spreadheet, it prints the first separately as single sided,
and then double sided the rest. Have set up new spreadsheet, and works fine.

Post your answer or comment

comments powered by Disqus
Hey everyone. I have a problem. I've searched the forum, and couldn't find an answer to my question, so I figured I'd go ahead and post it.

I need to print multiple sheets from a single workbook (that have appropriate print areas already set up). I want to assign this macro to a button, so that I can just click one button and get a copy of every sheet.

Each of the sheet names is unique, and stored in column A on a sheet titled LIST, since there are four sheets of stuff that don't need to print. Each of the uniquely-named sheets are hidden as well. What I need:

* Print all sheets, using the names on LIST in column A (A1 to the last one with data; the names are alphabetized from ADAM on down to WILLOW).
* Ask for confirmation 'Are you sure you want to print all saved sheets?'
** If yes, print all the sheets.
** If cancel, do nothing.

Any help would be appreciated. I can see how Id do everything but using the A column to define what names to print, I think.

Hi all,

I was wondering how i would go about modifying the code i have below so that
i can print multiple sheets to a single PDF file with each worksheet printed
being a different tab. (if that last part isn't possible, then 1 pdf file
with each page being a separate sheet from the workbook.)

Note: my workbook has 15 sheets, only 1 sheet is visible at a time. the
print function will unhide/hide the sheets so that they can be printed with
the code below. (not all 15 sheets will be printed to pdf, only 4)

here is the code i am using to print to a pdf file:

If printToPDF And (pageID(i) = "Invoice" Or pageID(i) = "Withdrawls" Or
pageID(i) = "Deposits" Or pageID(i) = "Sales Journal" Or pageID(i) =
"Summary" Or pageID(i) = "Inventory Costs") Then
pb.Caption3 = "Printing" & pageID(i)
Set myPDF = New PdfDistiller
PSfilename = path & ""
If pageID(i) = "Invoice" Then
PDFfilename = path & order & ".pdf"
PDFfilename = path & pageID(i + 2) & " " & Year(Date) &
End If
ActiveWindow.SelectedSheets.PrintOut copies:=1,
ActivePrinter:=ChoosePrinter(pdfPrinter), preview:=False, printtofile:=True,
collate:=True, prtofilename:=PSfilename
pb.Caption3 = "Converting to PDF...."
myPDF.FileToPDF PSfilename, PDFfilename, ""

Kill PSfilename
On Error Resume Next
Kill path & "*.log"
On Error GoTo 0
Set myPDF = Nothing
End If

i have tried printing each sheet to a .ps file and then using
mypdf.filetopdf to convert it, but i haven't been able to get it to accept
multiple input names.

If you need my entire printdocuments sub, let me know and i'll paste that


I have code to print out selected sheets from one workbook. Have it and it works great thanks to leahProton.

I now need to save those selected mulitple sheets as a single PDF file, so I found code to do that. (opver all problem is to spool all printing s a single job)

I can get each code to work separately, but not together.

I need some help, please!

Thanks, J

Here is my code to printout the selected sheets"
Sub report()
'When this procedure is run, it will:
'    select the QUOTE sheet
'    count the number of "Items"
'    lookup the item number on the summary sheet and copy the corresponding sheet name to the printlist array
'* added:
'    it will check quote sheet row 33 for an entry, if true, then add the INDIVIDUAL PRODUCT sheet
'    it will add the QUOTE sheet as the last sheet in the list to be printed.

     Dim PrintList()                                                       'array to hold sheets to print
     Dim SheetCount As Integer
     Dim ItemCount As Integer                                              'number of non-empty cells in Item column, i.e.,
A17 thru A29
     Dim TableRange                                                        'data on SUMMARY sheet
     Dim Item                                                              'current cell in list of QUOTE "Items"
     Dim ItemList                                                          'the "Item" column itself
     Set ItemList = Range("a17:a29")
     Set TableRange = Worksheets("Summary").Range("A7:G37")
     ItemCount = Application.WorksheetFunction.CountA(Range("a17:a29"))    'get the total number of items listed
     ReDim PrintList(1 To ItemCount)                                       'size the sheet list array to hold this many sheet
'Process the "Items" column:
'for each item, check if it has a corresponding sheet name, then,
'if so, copy the sheet name to the print list

     For Each Item In ItemList
          If Item.Value > " " Then                                         '<=== note: changed the test value
from "" (NULL) to " " (space)
               SheetCount = SheetCount + 1
               If Application.WorksheetFunction.VLookup(Item.Value, TableRange, 7) <> "" Then
                    PrintList(SheetCount) = Application.WorksheetFunction.VLookup(Item.Value, TableRange, 7)
                    SheetCount = SheetCount - 1
               End If
               Exit For
          End If
     Next Item
'---------------- mod ------------------------------
'The following if..then..else block will check for entries in row 33 of the quote sheet.  If there are, then it will
'add the A. INDIVIDUAL PRODUCTS sheet to the list along with the QUOTE sheet; else it just adds the QUOTE sheet.

     If Sheets("quote").Range("E33").Value > "0.00" Then
          SheetCount = SheetCount + 2
          ReDim Preserve PrintList(1 To SheetCount)
          PrintList(SheetCount - 1) = "A. INDIVIDUAL PRODUCTS"
          PrintList(SheetCount) = "QUOTE"
          SheetCount = SheetCount + 1
          ReDim Preserve PrintList(1 To SheetCount)
          PrintList(SheetCount) = "quote"
     End If
     Sheets(PrintList()).Select                             'select all sheets in the list
     ActiveWindow.SelectedSheets.PrintOut Copies:=1         'print the sheets
End Sub


Option Explicit

Sub PrintToPDF_MultiSheetToOne_Late()
'Author       : Ken Puls (
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from
'   Designed for late bind, no references req'd

    Dim pdfjob As Object
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim lTtlSheets As Long

    '/// Change the output file name here!  ///
    sPDFName = "Consolidated.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

    'Make sure the PDF printer can start
    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
            vbOKOnly, "Error!"
        Exit Sub
    End If

    'Set all defaults
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
    End With

    'Print the document to PDF
    lTtlSheets = Application.Sheets.Count
    For lSheet = 1 To Application.Sheets.Count
        On Error Resume Next 'To deal with chart sheets
        If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
            Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
            lTtlSheets = lTtlSheets - 1
        End If
        On Error GoTo 0
    Next lSheet

    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets

    'Combine all PDFs into a single file and stop the printer
    With pdfjob
        .cPrinterStop = False
    End With

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
    Set pdfjob = Nothing
End Sub

My problem is my code will only print one sheet befor going to the next counter. I'm trying to get to print multiple sheet equal to the value of cell J80. Before going on to the nect counter. Example if cell J80=5 it should print 5 sheet as one print job and then move to next counter

here is my code

A Big thanks in advance

     ' doprint Macro
    Dim i As Integer 
    Dim oCell As Range 
    strjobnumber = InputBox("Start in Job Number?", " First Job to 
    Print", 0) 
    endjobnumber = InputBox("Finish in Job Number?", " Last Job to 
    Print", 0) 
    For Counter = strjobnumber To endjobnumber 
        Range("L5").Value = Counter 
        ***Here I would Like To grabe the value of cell J80)*** 
        ****Here I would Like tp print the number of sheet equal To 
        the value of Cell J80 As one print job*** 
        ****Currently it will only print one sheet befor going To Next 
        ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, 
        Copies:=1, Collate _ 
    Next Counter 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Quick question--

Trying to print multiple sheets, using naming convention "Sheet16, Sheet15, etc." However, getting type mismatch error when using the array. Here is code so far:

    Worksheets(Array(Sheet15, Sheet16, Sheet17, _ 
    Sheet18, Sheet19, Sheet20, Sheet21, _ 
    Sheet22, Sheet23, Sheet24, Sheet25, _ 
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need to use the Sheet## naming convention as others may move the sheets or rename them. Any ideas?

By the way, it seems sensible to me to always name sheets by the Excel permanent name. Am I missing something? Or is it because of the errors I get in the above example?

Thanks for your help!

I am trying to find a way to select and print multiple sheets based on a criteria or a list.

I have a large worksheet with many sheets. Each sheet falls into one of three categories, and I want to be able to automatically print all tabs in each category.

I have all of the sheets rolled up into a summary where I have access to all of the sheet name and print criteria if that is helpful.


Hi there-

I want to print multiple sheets in array fashion so that the page numbers are correct on the print.

How can I change this so that my if statements will add to an array that I can print at once? (Again I'm looking for consecutive page #'s on the print but printing a number of workbooks that have a dynamic # of pages)

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=False
If wsLABORDetail.Range("A13").Value  "" Then
End If
If wsEXPENDetail.Range("A13").Value  "" Then
End If
If wsFixed_Travell.Range("F9").Value  "" Then
End If
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=False
For example this recorded macro prints the array, but I have no way of adding a criteria for printing as my if statements would do (here I am not using ws variables since I just recorded in macro):
Sheets(Array("INVOICE", "LABOR_Detail", "EXPEN_Detail", "Fixed_Travel")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=False

Does anyone know how to print multiple sheets of a workbook with one print job?

Thank you

Often users in my office select multiple sheets to print or for some other reason. Every once in a while someone will forget to ungroup the sheets before they start editing. This will cause the changes they make to happen in all the grouped sheets. This can corrupt the data in several sheets and waste a lot of time restoring them.

Is there a way I can have a message box or something pop up and tell the user they are about to edit a cell on multiple sheets? Thanks!


Plz help me out.....

I have to copy huge data from multiple sheets into a single sheet.

How can I do that......

because sheets are so many and it takes too much of time...

Plz help me out....



When I try to print multiple sheets by right clicking on the sheet tab,
selecting all and pressing print, I expect that the printer/excel will
recognise the print job as one document (ie if there are 14 pages which I
have chosen to print double sided I will get 7 physical sheets of paper with
printing on both sides, even if I chose 14 separate worksheets).

What I get is 14 work sheets, with any printer set up changes I request only
appearing on the first sheet (eg if I choose to print in colour rather than
my default black and white, it only affects the first page).

Am I expecting the wrong thing (is this how excel is supposed to act) or is
there a setting within excel that I need to change?

I do not have this problem with word even if I choose multiple files and
print through windows explorer.

Thanks, any help would be appreciated.

Fiona Nelson

I am using this code to print out multiple sheets but when I print it always starts with the first sheet and not the last which is what I want since my worksheets are the names of personnel in alphabetical order. So when I print I get the worksheets out of the order I want. I want the last name first so at the end of the printing the first name is on the top of the pile of pages. How do I change the code to print out the way I want? Thanks.

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Sheets(Array("Adams", "Brooks", "Collins")).PrintOut

End Sub

Someone suggested that I try :

sheets("Adams").printout sheets("Brooks").printout sheets("Collins").printout
When I do this Excel just prints out the first sheet it finds in the workbook not the order I want.


I have a workbook with multiple sheets and I would like to print this
workbook at the click of a button. Each sheet has a heading in cell c1, c2 is
blank and then a table begins in c3. Each table varies from 1 to 9 columns.
There is data in cols and and b that is not to be printed, but I would prefer
the empty columns still to appear.

Things get further complicated by the fact that some tables have a small
number of rows and some have up to one hundred. I need Excel to insert
"appropriate" page breaks so if there is space for the first three tables
print on a single sheet, the page break should come after table three.

Is this doable, I have been looking around but there seem to be a number of
suggestions but page breaks don't get a mention.

Thanks in advance for any suggestions,

Hi All,

I have VBA code in Excel 2007 that selects multiple sheets and then
does a print preview.

I've noticed that if I try to print two or more sheets with several
graphs and charts, the images don't appear or actually do a vanishing
act in the print preview. Specifically, they may initially appear
until you scroll down to view the preview for the next sheet's set of
pages, then all the charts/images disappear when you scroll back up.

Has anyone else experienced this issue? Any ideas on what could be
causing this. This doesn't occur in 2000 or 2003 versions.

Any thoughts would be greatly appreciated. Thanks.

Best regards,


Hi, Me again, last year I made a spreadsheet for my pool league.

I would like to automate something but don't know where to begin this (for most of you) simple task.

I included my work, and the password to unlock the sheets in Moose .

What I would like to do is print the first 25 sheets on one side and sheet 26 (Player DATA) in the rear of those 25 sheets and then print sheet 27 (Marie-Ève) by itself.

There must be a way to do this using a macro or something that when I press print (or a custom button) the paper will magically appear on my printer ?

I'm not really good at excel but I learn quite fast.

And also, the first 25 sheets, are printed normally but on the reverse where Player DATA is printed, it has to be printed in draft mode (light printing).

I already have all the printing settings saved in my pc as I manually print them every weeks, I print the first 25, then re insert them then print player data and I then print Marie-Ève.

I use an Epson Artisan 800 with the auto duplexer attachment, Windows 7 64bits ultimate and office 2007.

Please help me, I'm lost on this one . . .

Windows XP SP2
Excel 2007 SP2
PDFCreator 0.9.3


I have been using the Ken Puls' (ExcelGuru) code for creating a single PDF file from multiple worksheets found here:

SUMMARY - Ken's code assumes that jobs added sequentially to the PDFCreator job queue are cumulative, however my problem is that old jobs drop out of the queue before new jobs are added.

I have posted a problematic excerpt of the code and the output.
    Dim i As Integer
    Dim TimerStart As Long
    TimerStart = Timer
    Dim oldPrintCount As Integer
    oldPrintCount = pdfjob.cCountOfPrintjobs
    Debug.Print "lTtlSheets: " & lTtlSheets
    Debug.Print "Initial Printjob Count: " & oldPrintCount & " at " & Timer
    If oldPrintCount > 0 Then
        For i = 1 To pdfjob.cCountOfPrintjobs
            Debug.Print pdfjob.cPrintjobFilename(i)
        Next i
    End If

    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets _
        Or (Timer - TimerStart > TimerInterval2 And pdfjob.cCountOfPrintjobs = 0)
        If oldPrintCount <> pdfjob.cCountOfPrintjobs Then
            Debug.Print "Previous Printjob Count: " & oldPrintCount
            Debug.Print "Current Printjob Count: " & pdfjob.cCountOfPrintjobs & " at " &
            oldPrintCount = pdfjob.cCountOfPrintjobs
            For i = 1 To pdfjob.cCountOfPrintjobs
                Debug.Print pdfjob.cPrintjobFilename(i)
            Next i
        End If
So - this code executes once all the required worksheets (3 of them) have had the PrintOut method invoked. I've added some debugging code to illustrate my problem.

lTtlSheets: 3
Initial Printjob Count: 1 at 45197.67
C:Documents and Settingspootle_flumpLocal SettingsTempPDFCreatorPDFCreatorSpool~PS11C.tmp

Previous Printjob Count: 1
Current Printjob Count: 0 at 45197.94

Previous Printjob Count: 0
Current Printjob Count: 1 at 45198.44
C:Documents and Settingspootle_flumpLocal SettingsTempPDFCreatorPDFCreatorSpool~PS121.tmp

Previous Printjob Count: 1
Current Printjob Count: 0 at 45199.14

Previous Printjob Count: 0
Current Printjob Count: 1 at 45199.64
C:Documents and Settingspootle_flumpLocal SettingsTempPDFCreatorPDFCreatorSpool~PS126.tmp

Previous Printjob Count: 1
Current Printjob Count: 0 at 45200.41
As such, jobs appear to be getting added to the queue but then being processed before the rest of jobs are being added. The result is that the PDF created only contains one of the worksheets. I added the check against the Timer as part of the loop condition because my code was looping forever.

This is an intermittent problem - when I ran this the first couple of times getting the stuff together for this post it ran perfectly!

Any help would be gratefully received.


I'm trying to find a way of printing multiple worksheets at one time (my goal is to save them as a single pdf file from the print menu). I know how to select multiple sheets and then print, but this results in multiple print jobs or pdf files. Would it make sense to create a new worksheet that references all of the other sheets in sequence? When I've tried this, I've had some problems maintaining the correct formatting in the new sheet. I would, however, like to keep the ability to modify individual sheets (this helps manage the 15-20 pages of data and graphs).

Any thoughts?


I am fairly new to VBA and am trying to create a user form that enables the user to select multiple worksheets for print using a multiselect listbox.

I have already written the code needed to update the listbox with the worksheets in the workbook:

Private Sub UserForm_Initialize()
With frmPrintToPDF
lbxSheetNames.RowSource = "SheetNames"
End With
End Sub

I need some help with the code i should apply to the listbox to allow the user to select multiple sheets. The code I have so far is this:

Private Sub lbxSheetNames_Click()
End Sub

The code above worked for a single selection listbox, but not for a multi select listbox.

Ideally the user will be able to highlight as many sheets as they want in the list box to print to PDF, click "Print to PDF" in the userform and the user will then be prompted to determine the location and name their file, creating a PDF of the sheets they highlighted.

Also, I am not sure if I should use a multiselectMulti or a multiselectExtended list box.

Any help is greatly appreciated!

I'm selecting all 3 sheets, file/print to the printer "Adobe PDF" and it gives the save as dialog box, but as soon as I hit OK the save dialog box comes up again, and the first sheet goes into the first place, and the other 2 go to the other. Anyone seen or heard of this? XL03; Adobe Acrobat 6.0 standard.

P.S. - I don't condone PDFs, which are the bane of working with data - but a client insists on it. Tragically, rather than publishing data directly - in TXT, HTM, DOC or whatEVER! - I think that people mindlessly put data printouts on scanners and save to PDF. Government sources, loaded with rich data that we were all bled to pay for via tax, are the worst at this. Tragic.

When printing mutiple selected sheets in Excel, though the total no of pages
in footer changes, but the individual page no. does not increse say 1,2,3,4
etc., it remains for example 1/4, 2/4, 1/4, 1/4 where there are three sheets
, first one containing two pages and the other two one each.
When I print individual sheets footer shows 1/2, 2/2, 1/1, 1/1.

Hi -

I'm trying to create a macro that allows me to print certain sheets from a worksheet, whereby the names of the sheets I want to print are contained in a range (ie "printrange")

I'm not very familiar with writing code but I tried to modify an existing macro I had, which cycles through the sheets that I've listed out in the range and prints it one by one (pls see below). What I would like some help on, is to create a macro which effectively selects multiple sheets and issues a print command. This is so that when I use it to print PDF files, I don't get multiple PDF files. I just can't figure out how to get the name of the sheets (which are not always the same - ie may want to print out different pages of output) into an array...

Thanks in advance!

Old macro:

Sub PrintMacro()

Dim PrintRange As Range
Set PrintRange = Range("SheetsToPrint")

confirm = InputBox("Are you sure? (enter 'y' to continue)")

If confirm <> "y" Then GoTo TheEnd

For Each X In PrintRange
If X.Value <> "" Then
Worksheets(X.Value).PrintOut Copies:=1, Collate:=True
End If
Next X


End Sub

I'm using Excel 2002 in Windows XP.

I have a large Excel application where users choose to print one or
more sheets. They do this by using the Ctrl key and the mouse to
highlight multiple tabs and then hitting the Print button.

Each worksheet has a defined print area and is set up to fit 1 by X
pages (most are 1 by 1). Each worksheet also has either landscape or
portrait set in the Page Setup, depending on the page layout.

If I choose to print a single sheet that is 1 by 1/Landscape, it prints
fine. If I select this same sheet along with another that is 1 by
3/Portrait. The Landscape sheet will print in Portrait. Sometimes the
Portrait sheet prints in Landscape. I haven't been able to narrow down
why sometimes Portrait wins and sometimes Landscape wins--it doesn't
seem to be affected by which sheet is active.

This only happens on a particular printer: HP LaserJet 8000 Series PCL
6. I'm going to contact our IT department to see if the driver is
updated for that printer.

If anyone has any other ideas what is at issue, I'd appreciate hearing


I have three computers on the network. These computers were just replaced a couple of weeks ago, beforehand everything worked fine. One computer I have no problems with and it works fine. The other two computers, they will randomly print double sided when in Excel. Yesterday they were printing double sided, today, they are printing single sided. I want them to print single sided all of the time.

Since it’s only two of the three computers that are having this problem, it’s not the printer (they all print to the same printer).

I’ve checked the part under Print > Properties > Layout and selecting Single Sided. Is there another option in Excel where I can define to print single sided only?

Thanks guys

Hi Everyone,

First time posting. This site has help me so much without asking a thing but now I'm stuck.

I have a Workbook that has 8 sheets that need to be printed out. Most of the time the sheets are only 1 page long but can get up to 3 pages long. What I'm trying to do is print the sheet according to how many pages have data on them. I can not get it to work, here is the code I am using:

    Dim y As Integer 
    Dim x As Integer 
    Dim z As Integer 
    y = A27 
    x = A55 
    z = A8 
    If Not Worksheets("Surry Hills").Cells(x, 1) = "" Then Worksheets("Surry Hills").PrintOut from:=1, To:=3 
ElseIf Not Worksheets("Surry Hills").Cells(y, 1) = "" Then Worksheets("Surry Hills").PrintOut from:=1, To:=2 
ElseIf Not Worksheets("Surry Hills").Cells(z, 1) = "" Then Worksheets("Surry Hills").PrintOut from:=1, To:=1 
    If Not Worksheets("Padstow").Cells(x, 1) = "" Then Worksheets("Padstow").PrintOut from:=1, To:=3 
ElseIf Not Worksheets("Padstow").Cells(y, 1) = "" Then Worksheets("Padstow").PrintOut from:=1, To:=2 
ElseIf Not Worksheets("Padstow").Cells(z, 1) = "" Then Worksheets("Padstow").PrintOut from:=1, To:=1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Just so you know A27 and where Page 2 start and A55 and where page 3 starts. Any ideas why its not working?

Thanks in advance,


No luck finding an answer? You could always try Google.