Printing Multiple Sheets

This page describes VBA code to print multiple sheets with one operation.

Introduction

When you print a sheet in Excel, that sheet is printed in its own print job. Printing multiple worksheets therefore creates several print jobs. This page describes code you can use to print multiple worksheets as a single print job. The first parameter to all functions is named Preview and indicates whether the sheets should be displayed in the Print Preview window (Preview = True) or sent directly to the active printer (Preview = False).

PrintSheets

This function takes as parameters the names of the worksheets to print. Example usage:

    PrintSheets False, "Sheet1", "Sheet3", "Sheet5"
    

This prints sheets Sheet1, Sheet3, and Sheet5.
The code for PrintSheets is shown below.

    Sub PrintSheets(Preview As Boolean, ParamArray SheetNames() As Variant)
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' PrintSheets
    ' This prints all sheets passed in as parameters.
    ' It ignores sheets that do not exist.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Arr() As String
    Dim N As Long
    Dim K As Long
    Dim B As Variant
    Dim WS As Object
    If UBound(SheetNames) >= LBound(SheetNames) Then
        ReDim Arr(LBound(SheetNames) To UBound(SheetNames))
        K = LBound(SheetNames)
        For N = LBound(SheetNames) To UBound(SheetNames)
            On Error Resume Next
            Err.Clear
            Set WS = Sheets(SheetNames(N))
            If Err.Number = 0 Then
                Arr(K) = SheetNames(N)
                K = K + 1
            End If
            On Error GoTo 0
        Next N
        If K > 0 Then
            ReDim Preserve Arr(LBound(Arr) To K - 1)
            Sheets(Arr).PrintOut Preview:=Preview
        End If
    End If
End Sub

PrintSelectedSheets

This prints all the sheets that are selected. The code is shown below.

Sub PrintSelectedSheets(Preview As Boolean)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PrintSelectedSheets
' This prints all selected sheets.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim N As Long
    Dim M As Long
    Dim Arr() As String
    
    With ActiveWindow.SelectedSheets
        ReDim Arr(1 To .Count)
        For N = 1 To .Count
            Arr(N) = .Item(N).Name
        Next N
    End With
    Sheets(Arr).PrintOut Preview:=True
End Sub

PrintUnSelectedSheets

This prints all the sheets that are not selected. The code is shown below.

Sub PrintUnselectedSheets(Preview As Boolean)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PrintUnselectedSheets
' This prints all unselected sheets.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim WS As Object
    Dim N As Long
    Dim Arr() As String
    Dim K As Long
    Dim B As Boolean
    
    ReDim Arr(1 To ActiveWorkbook.Sheets.Count)
    For Each WS In ActiveWorkbook.Sheets
        B = True
        With ActiveWindow.SelectedSheets
        For N = 1 To .Count
            B = True
            If StrComp(WS.Name, .Item(N).Name, vbTextCompare) = 0 Then
                B = False
                Exit For
            End If
        Next N
        If B = True Then
            K = K + 1
            Arr(K) = WS.Name
        End If
        End With
    Next WS
    If K > 0 Then
        ReDim Preserve Arr(1 To K)
        ActiveWorkbook.Sheets(Arr).PrintOut Preview:=Preview
    End If
End Sub

PrintSheetsExclude

This procedure prints all sheets except those whose names are passed in as parameters. Example usage:
PrintSheetsExclude false, "Sheet2", "Sheet4", "Sheet6"
This prints all sheets except Sheet2, Sheet4, and Sheet6. The code is shown below:

Sub PrintSheetsExclude(Preview As Boolean, ParamArray Excludes() As Variant)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PrintSheetsExclude
' This prints all sheets except those included as parameters.
' It is legal for Excludes to include sheet names that
' do not exist. If no sheet names are passed in, all
' sheets are printed.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Arr() As String
    Dim B As Boolean
    Dim N As Long
    Dim M As Long
    Dim K As Long
    
    ReDim Arr(1 To Sheets.Count)
    For N = 1 To Sheets.Count
        B = True
        For M = LBound(Excludes) To UBound(Excludes)
            If StrComp(Sheets(N).Name, Excludes(M), vbTextCompare) = 0 Then
                B = False
                Exit For
            End If
        Next M
        If B = True Then
            K = K + 1
            Arr(K) = Sheets(N).Name
        End If
    Next N
    If K > 0 Then
        ReDim Preserve Arr(1 To K)
        Sheets(Arr).PrintOut Preview:=Preview
    End If
End Sub

You can download the file with all the example code on this page.

This page last updated: 29-January-2011.