Printing Certain Pages

The PrintOut method applies to a lot of different objects. You can print a Workbook, Worksheet, Chart, Collection of Worksheets, Collection of Charts, Window object and even a Range.

PrintOut has some useful arguments including the From and To arguments. These let you define which pages will print. If, for instance, you want to print every worksheet in your workbook, but only want to print the first page, you might use a sub like this:

Sub PrintPage1()

    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        ws.PrintOut From:=1, To:=1
    Next ws
    
End Sub

This will create a separate print job for every page it prints. If you want to print a lot of pages in one print job, you can use this method. You won’t be able to specify pages, though. If you tried to specify only the first page using that method, it would only print the first page of the whole print job, not the first page of every worksheet.

Posted in Uncategorized

4 thoughts on “Printing Certain Pages

  1. Hello Dick,

    I often use the “.select Replace:=false” code to selectively send individual sheets to the printer as one print job. Here’s an example of how to print all of the visible sheets in a workbook as one print job:

    Sub PrintSheets()
    For Each sht In Worksheets
    If sht.Visible Then
    sht.Select Replace:=False
    End If
    Next sht
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End Sub

    The code can be modified to pick and choose what sheets you want to print.

    John Mansfield

  2. How can you determine how many Printable pages are there in a excel workbook? Workbook.count only tells you how many sheets there are?

    Thank You

  3. You have to use the old XL4Macro commands to get this information.

    GET.DOCUMENT(50)

    tells you how many printed pages your worksheet has. In VBA you have to construct it like this:

    Application.ExecuteExcel4Macro(“GET.DOCUMENT(50)”)

    – Jon


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

Leave a Reply

Your email address will not be published.