Printing Multiple Sheets

To print multiple sheets in the UI, you can use the Control and Shift keys to select multiple sheets, then print normally. In VBA, use an array of the sheets names as the argument to the Sheets property.

Below are two examples of how to do this. In the first, an array is assigned to a variant variable using the Array function. This allows you to specify which sheets are printed by identifying the sheet names as arguments to the Array function.

The second example loops through all the sheets and adds their names to an array. This particular example prints all the sheets in the workbook, but you could easily add an If statement to limit which sheets are added, and thus printed. In the end, you want an array of sheet names for only those sheet you want printed.

27 thoughts on “Printing Multiple Sheets

  1. Hi Dick,

    Here is an alternative that doesn’t require an array to store the sheetnames.

    Sub PrintSheetsLoop2()
    ‘ No need for array of names
    Dim sh As Object
    Dim lShCnt As Long

    Set sh = ThisWorkbook.ActiveSheet ‘ remember current
    For lShCnt = 1 To ThisWorkbook.Sheets.Count
    ThisWorkbook.Sheets(lShCnt).Select False
    Next lShCnt

    ThisWorkbook.PrintOut
    sh.Select True ‘ release grouped sheets
    End Sub

  2. Andy and Dick,

    It looks as if both approaches might be necessary. If all the sheets needed printing, then Andy’s approach seems best. However, if only a select set (say 10 out of 25 worksheets) needed printing then the array approach seems best.

    Have I understood both correctly?

  3. Hi shades,

    Either approach will work for all or a subset of sheets.
    You just have to add an IF THEN test within the loops to select or exclude sheets as required.

    Although with the array method you will have to have a separate counter and redim the array as required.
    Sub PrintSheetsLoop()
    Dim aShtLst() As String
    Dim sh As Object
    Dim lShCnt As Long
    Dim lCount As Long

    ‘ every other sheet
    For lShCnt = 1 To ThisWorkbook.Sheets.Count
    If lShCnt Mod 2 = 1 Then
    lCount = lCount + 1
    ReDim Preserve aShtLst(1 To lCount) As String
    aShtLst(lCount) = ThisWorkbook.Sheets(lShCnt).Name
    End If
    Next lShCnt

    ThisWorkbook.Sheets(aShtLst).PrintOut
    End Sub

    Cheers
    Andy

  4. If all sheets are to be printed out

    Sub PrintAll()
    Sheets.printout
    End Sub
    or
    Sub PrintAllWorksheets()
    Worksheets.Printout
    End sub

    for Andy’s first method, it assumes the currently selected sheet is to be printed out. Additional code would be needed if that were not the case. It is not difficult – a boolean flag would be sufficient.

    Sub PrintSheetsLoop2()
    ‘ No need for array of names
    Dim sh As Object
    Dim lShCnt As Long
    Dim bFlag As Boolean
    bFlag = True

    Set sh = ThisWorkbook.ActiveSheet ‘ remember current
    For lShCnt = 1 To ThisWorkbook.Sheets.Count
    If lShCnt Mod 2 = 1 Then
    ThisWorkbook.Sheets(lShCnt).Select bFlag
    bFlag = False
    End If
    Next lShCnt

    ThisWorkbook.PrintOut
    sh.Select True ‘ release grouped sheets
    End Sub

    –
    Regards,
    Tom Ogilvy

  5. I have multiple worksheets containing single charts. I need to print 4 charts to a PAGE, so I need to print 4 worksheets to one page. Any suggestions?!?!!? Thanks

  6. how do I make aq hyperlink to print multiple pages I specity?

    Example: print sheet1,sheet3,sheet6,sheet8

    so when I press the hyperlink it will print those 4 sheets.

    Pleaese let me know if you can fgure this out

    Thankyou

  7. Hi All,

    I want to create a array of sheets with if condition.

    Suppose in a excel file there are n number of sheets, I want to create a macro which select only those sheets name which are present in a file with if condition. I do not want to pass array manually and do not want to print all the sheets in a file.

    I want to print selected sheets out of all the sheets present in a file.

    Thanks in Advance.
    Avi

  8. I’ve been trying to apply this and my code is as follows:-

    Sub PrintComp()
    Dim Printsheets As String
    Dim APrintSheets As Variant
    Printsheets = “”
    If Worksheets(“Menu”).Cells(17, 4) = “Yes” Then Printsheets = Printsheets & “Comp” & Chr(34)
    ‘End If
    If Worksheets(“Menu”).Cells(18, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Cap Allces” & Chr(34)
    ‘End If
    If Worksheets(“Menu”).Cells(19, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Notes” & Chr(34)
    ‘End If
    If Worksheets(“Menu”).Cells(20, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Q7? & Chr(34)
    ‘End If
    If Worksheets(“Menu”).Cells(22, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Ind Bldg Allces” & Chr(34)
    ‘End If
    If Worksheets(“Menu”).Cells(23, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Ag Bldg Allces” & Chr(34)
    ‘End If
    If Worksheets(“Menu”).Cells(24, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Def Tax” & Chr(34)
    ‘End If
    If Worksheets(“Menu”).Cells(25, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Proof”
    ‘End If
    APrintSheets = Array(Printsheets)
    ThisWorkbook.Sheets(APrintSheets).PrintOut
    End Sub

    I tried it with “ThisWorkbook.Sheets(Array(PrintSheets)).PrintOut” first of all but that failed as well. The error message I get is “Subscript out of Range”. The PrintSheets String comes out as “Comp”,”DefTax”,”Proof” with the current settings so that appears to be valid but I’m not sure why I’m getting the error message. Needless to say, I’m trying to select multiple but not necessarily consecutive sheets and based on user selection. Any reason why this doesn’t work when it appears to be similar to the first example shown on this thread?

    My ultimate aim with this is to save paper when printing to a duplex printer ie when I used a macro to print selected sheets individually (based on user selection) I got each sheet on a separate sheet of paper (each sheet is just a single page) whereas when selecting manually it is possible to get the pages printed on both sides of the paper on a duplex printer. Will this macro achieve that objective or am I barking up the wrong tree anyway?

  9. Did SleeplessInSomerset ever get an answer? I’m having the exact same problem. I’ve created a dynamic string that I want to pass into an Array so that I can print a number of worksheets at once. But the Array always barfs. What should I be generating with my If Thens?

  10. Hi,
    I love your blog and all the great relevant information I can found in it about Excel and VBA.
    In order to “save” some paper I made a custom Macro Printing 4 Pages (selecting the print area from each page) in 1 Pages. The code is working for a Workbook with up to 24 pages:

    Sub Print4To1()

    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Sheets(“PrintView”).Delete
    Application.DisplayAlerts = True
    ‘Add a worksheet with the name “PrintView”
    Set Basebook = ThisWorkbook
    ActiveWorkbook.Worksheets.Add after:=Worksheets(Sheets.Count)
    Worksheets(Sheets.Count).Name = “PrintView”

    RowANbr = 1
    RowBNbr = 1
    ColANbr = 1
    ColBNbr = 1

    TotalSheet = ThisWorkbook.Worksheets.Count – 1

    For i = 1 To TotalSheet – 1
    Sheets(i).Select
    myrange = Sheets(i).PageSetup.PrintArea
    MyRangeRowNbr = Range(myrange).Rows.Count
    MyRangeColNbr = Range(myrange).Columns.Count
    Range(myrange).Copy
    If i = 1 Then
    Sheets(“PrintView”).Select
    Cells(RowANbr, 1).Select
    Sheets(“PrintView”).Pictures.Paste(Link:=True).Select

    For k = 1 To MyRangeRowNbr
    Sheets(“PrintView”).Cells(k, 1).RowHeight = Sheets(i).Cells(k, 1).RowHeight
    Next k
    For k = 1 To MyRangeColNbr
    Sheets(“PrintView”).Cells(1, k).ColumnWidth = Sheets(i).Cells(1, k).ColumnWidth
    Next k
    ‘—- Save Ligne and column where Sheet 1 stop stop in preview sheet
    RowANbr = RowANbr + MyRangeRowNbr + 2
    ColANbr = ColANbr + MyRangeColNbr + 1
    Else
    If xIsEven(i) = True Then
    Sheets(“PrintView”).Select
    If RowBNbr TotalRowB Then
    TotalRow = TotalRowA
    Else
    TotalRow = TotalRowB
    End If

    Sheets(“PrintView”).Activate
    ActiveWindow.View = xlPageBreakPreview
    Sheets(“PrintView”).DisplayAutomaticPageBreaks = True

    With Sheets(“PrintView”).PageSetup
    .PrintArea = Range(Cells(1, 1), Cells(TotalRow – 1, TotalCol – 1)).Address
    ‘ Range(Cells(1, 1), Cells(TotalRow, TotalCol))
    .PrintTitleRows = “”
    .PrintTitleColumns = “”
    .LeftMargin = Application.InchesToPoints(0.196850393700787)
    .RightMargin = Application.InchesToPoints(0.196850393700787)
    .TopMargin = Application.InchesToPoints(0.196850393700787)
    .BottomMargin = Application.InchesToPoints(0.196850393700787)
    .HeaderMargin = Application.InchesToPoints(0)
    .FooterMargin = Application.InchesToPoints(0)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    ‘ .PrintQuality = 300
    .CenterHorizontally = True
    .CenterVertically = True
    .Orientation = xlPortrait
    .Draft = False
    .PaperSize = xlPaperA4
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = i
    .PrintErrors = xlPrintErrorsDisplayed
    End With

    For NbrDePage = 1 To (i – 1)
    j = 1
    myrange1 = Sheets(j * NbrDePage).PageSetup.PrintArea
    TotalRow1 = Range(myrange1).Rows.Count
    myrange2 = Sheets(j * NbrDePage + 2).PageSetup.PrintArea
    TotalRow2 = Range(myrange2).Rows.Count
    myrange3 = Sheets(j * NbrDePage + 1).PageSetup.PrintArea
    TotalRow3 = Range(myrange3).Rows.Count
    myrange4 = Sheets(j * NbrDePage + 3).PageSetup.PrintArea
    TotalRow4 = Range(myrange4).Rows.Count
    TotalRowPageOdd = TotalRow1 + TotalRow2
    TotalRowPageEven = TotalRow3 + TotalRow4
    If TotalRowPageOdd

  11. Im using MS EXCEL.
    I prepared suppose 5 sheet.
    Sheet 1
    Sheet 2
    Sheet 3
    Sheet 4
    Sheet 5

    i want to get printout of Sheet 1,2,3,4,
    How it works
    How can i get printout of that sheets by using (only 1) button or one command

    Can i any body guide me.
    I m thankful to.

  12. Im using MS EXCEL.
    I prepared suppose 5 sheet.
    Sheet 1
    Sheet 2
    Sheet 3
    Sheet 4
    Sheet 5

    i want to get printout of Sheet 1,2,3,4, ( i don’t want to get printoout of Sheet 5)
    How it works
    How can i get printout of that sheets by using (only 1) button or one command

    Can i any body guide me.
    I m thankful to.

  13. Note to Osama: If you’re using the same workbook every time you print, I think you could create a macro to do the following:
    1. Select all but the last sheet
    2. Print
    3. Select a single sheet (so that you won’t accidentally edit all sheets at once)

    Then you could assign that macro to a macro button.

  14. I have the following code to print a calendar by day. It’s done in a template in one worksheet. As such, each loop produces one print job for each calendar day. I want to do duplex printing. How do I structure the codes so that all 366 days can be printed in one print job.

    Many thanks
    Rebecca

    Sub Calendar()
    Dim i As Integer
    For i = 0 To 365
            Range(“I1”) = DateAdd(“d”, i, “1/1/2010”)
            ActiveSheet.PrintOut
        Next i
    End Sub
  15. REDIRECT SELECTION IN CELL TO PRINT JOB

    In spreadsheet_1 I’m creating several cells which contain drop-down menus so the user can select values from other spreadsheets in the same workbook (Excel 2003).

    My issue is:

    I would like the user to, once all data is selected, be able to print automatically the spreadsheet_1 AND all the spreadsheets selected in those cells with the drop-down menu when he selects print… Is it possible?

    I’m new to programming and VBA, but I’m keen to learn!

    Thanks in advance!

    millawitch

  16. I was trying to print 35 pages of a spreadsheet through the click of a vba macro on the first page. When I used the above code, it didn’t work :( Im very new at this, but I think I typed it in correctly. Could you explain why this wouldn’t work?

    Sub PrintSheets()
    Dim MyArray As Variant

    MyArray = Array(“Title Page”, “Summary”, “Balance Sheet”, “Bal Sht Desc”, “Asset vs. Sales Trends”, “Asset vs. Liab & Equity Trends”, “Pct Bal Sht Desc”, “Pct Bal Sht”, “Current”, “Quick”, “Receivable turn”, “Inventory turn”, “Payable turn”, “Sales Fixed Assets”, “Sales Total Assets”, “Debt to Worth”, “Income Statemtent”, “P&L Trends”, “EBITDA Trend”, “Pct P&L Desc”, “Pct P&L”, “Depreciation as a & of Sales”,
    “% Profit Assets”, “%Profit Net Worth”, “Times Interest Earned”, “Funded Debt to EBITDA”, “Cumulative Growth Rate”, “Cash Flow Statement”, “Cash Flow Desc”, “Cash Flow”, “Sales Working Capital”, “Debt Service Coverage”, “Z score”, “EM Score new”, “Conclusion”)

    ThisWorkbook.Sheets(MyArray).PrintOut

    End Sub

    I look forward to your response, by the way these titles are the titles of each sheet I would like to print. Is this wrong?

  17. hola para todos miren he creado un programa en base de formulas para algo aqui en colombia que se llama gota gota que son prestamos a corto tiempo tengo lobros con 110 clientes y un listado de cobro tambien con recibos pero en cada hoja tengo un recibo igual cada cliente es una hoja quiero una macro que selecione area de imprecion e imprima los 110 recibos ha y si tengo 60 clientes como hago aque el numero 61 no se imprima
    gracias por su aayuda

    hector

  18. Hi there,

    I’m glad I found this post. The first code is exactly the one I was looking for.

    However I need a small modification as I want to be able to print the sheets I want despite they are actually hidden.

    How can include that in the following code? Many thanks

    Sub PrintSheets()

    Dim aShtLst As Variant

    aShtLst = Array(“Sheet1”, “Sheet2”, “Sheet3”)

    ThisWorkbook.Sheets(aShtLst).PrintOut

  19. Something like this

  20. I’m gaining a lot from this feed, but I’m at the next stage. My client has a spreadsheet with say 20 tabs in it and wants say 10 of those tabs to print to one PDF report, so 10 tabs are printed and a single PDF file is created. The issue is that the client knows they will be adding and removing tabs from the workbook which means the tabs they want to print will change from one month to the next. I noticed the line in the above code:

    aShtLst = Array(“Sheet1”, “Sheet2”, “Sheet3”)

    I have set up an area on a worksheet, say cells L21 to L40 where the user can specify which tabs they want in the report and what order they want them in – I’ve allowed 20 cells but realistically the client will not use all of these. Rather than using Array(“Sheet1”, etc. is there a way using VBA to read in the values in the cells L21 to L40 and generate one PDF report containing the spreadsheets on all of these tabs.

  21. David: Something like this

  22. Thanks Dick. A few tweaks needed but it worked well. Thanks. You filled the gap in my knowledge to get this done. Regards, David


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.