Examples to print worksheets

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

Copy the code in a Standard module of your workbook, if you just started with VBA see this page.
Where do I paste the code that I find on the internet

Print visible, Hidden or all worksheets

If you want to print a whole workbook you can use ThisWorkbook.PrintOut Or ActiveWorkbook.PrintOut But this will not print hidden Worksheets. You can use this macro to print hidden and visible Worksheets

Sub Print_Hidden_And_Visible_Worksheets()
    Dim CurVis As Long
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        With sh
            CurVis = .Visible
            .Visible = xlSheetVisible
            .PrintOut
            .Visible = CurVis
        End With
    Next sh
End Sub

To print only hidden sheets use

        With Sh
            CurVis = .Visible
            If CurVis >= 0 Then
                .Visible = xlSheetVisible
                .PrintOut
                .Visible = CurVis
            End If
        End With

Print the same worksheet ? time with the number in cell, Header or Footer

This example will print ? copies of the same sheet (It use a Input box to ask you how many)
It will copy the page number in cell A1 or in the Header or Footer.

Note: you can use this for testing .PrintOut preview:=True

Sub PrintCopies_ActiveSheet_1()
    Dim CopiesCount As Long
    Dim CopieNumber As Long
    CopiesCount = Application.InputBox("How many copies do you want", Type:=1)

    For CopieNumber = 1 To CopiesCount
        With ActiveSheet
            ' This example print the number in cell A1
            .Range("A1").Value = CopieNumber & " of " & CopiesCount

            'If you want the number in the footer use this line
            '.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

            'Print the sheet
            .PrintOut
        End With
    Next CopieNumber
End Sub

The example below continue printing where It left off, such as today you print numbered pages 1-25 and the next time when you enter 10 in the input box it print 26-35.

Sub PrintCopies_ActiveSheet_2()
' This example print the number in cell A1
    Dim CopiesCount As Long
    Dim CopieNumber As Long
    CopiesCount = Application.InputBox("How many copies do you want", Type:=1)

    With ActiveSheet
        If Not IsNumeric(.Range("A1").Value) Then .Range("A1").Value = 0

        For CopieNumber = 1 To CopiesCount
            .Range("A1").Value = .Range("A1").Value + 1

            'Print the sheet
            .PrintOut

        Next CopieNumber
    End With
End Sub

Print every Worksheet with a value In cell A1

With this macro you loop through every worksheet and if there Is a value in a certain cell it will add the sheet to the array and print it. You can also test a for a word like Sh.Range("A1").Value = "PrintMe"

Sub Print_All_Worksheets_With_Value_In_A1()
    Dim Sh As Worksheet
    Dim Arr() As String
    Dim N As Integer
    N = 0
    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Visible = xlSheetVisible And Sh.Range("A1").Value <> "" Then
            N = N + 1
            ReDim Preserve Arr(1 To N)
            Arr(N) = Sh.Name
        End If
    Next
    With ActiveWorkbook
        .Worksheets(Arr).PrintOut
    End With
End Sub

One liners to Print

Look in the VBA help for PrintOut and see that you can use the following arguments.
expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

Note: Remember that you can't print sheets that are hidden. If you print the whole workbook with the first example there is no problem but the example for Sheets or worksheets (example 2 and 3) will not work if there are hidden sheets.

ActiveWorkbook.PrintOut
'the whole workbook

Worksheets.PrintOut
'all worksheets

Sheets.PrintOut
'all sheets

Sheets(Array("Sheet1", "Sheet3")).PrintOut
'all sheets in the array

ActiveWindow.SelectedSheets.PrintOut
'print all selected sheets

ActiveSheet.PrintOut
'only the activesheet

Sheets("Sheet1").PrintOut
'only "Sheet1"

Selection.PrintOut
'print only the selection

Range("C1:D5").PrintOut
'print range

Ron de BruinExcel Automation