[Excel VBA] 4 Basic Patterns to Automate Printing (Sheet, Workbook, Range)

2025年12月29日

When creating routine reports or documents, manually selecting the same sheets or cell ranges to print every time is time-consuming.

Using VBA, you can automate this process with a single “Print” button. This allows you to print specific targets with specific settings instantly, greatly improving work efficiency.

This article explains how to use the .PrintOut method, which is the foundation of printing automation in VBA. We will cover four different targets: the active sheet, multiple sheets, the entire workbook, and specific ranges.

Pattern 1: Print the Currently Active Sheet

This is the most basic pattern. It prints one copy of the sheet the user is currently viewing or working on.

Sub PrintActiveSheetOnly()
    ' Print the currently active sheet
    ActiveSheet.PrintOut
End Sub

Key Points

ActiveSheet is an object that refers to the currently selected worksheet. By simply calling its .PrintOut method, the sheet is output from the default printer.

Pattern 2: Print Multiple Specified Sheets Together

This is useful when the content you want to print is divided into multiple sheets, such as a “Cover Page,” “Table of Contents,” and “Summary.”

Sub PrintMultipleSheets()
    ' Print "MonthlyReport" and "DataSheet" together
    Worksheets(Array("MonthlyReport", "DataSheet")).PrintOut
End Sub

Key Points

By passing sheet names or index numbers as an array (using the Array function) to the Worksheets object, you can select multiple sheets at once and print them together.

Pattern 3: Print the Entire Workbook with Multiple Copies

This prints all sheets contained in the workbook with a specified number of copies.

Sub PrintEntireWorkbook_3_Copies()
    ' Print the entire workbook 3 times
    ActiveWorkbook.PrintOut Copies:=3
End Sub

Key Points

ActiveWorkbook Object: When you use the .PrintOut method on the ActiveWorkbook object, the entire workbook becomes the print target.
Copies Argument: You can control the number of copies by specifying a number for the Copies argument of the .PrintOut method.
Other Useful Arguments: You can also specify other settings. For example, Preview:=True shows the print preview, and To:=5 prints only up to the 5th page.

' Example: Show print preview up to page 5
ActiveWorkbook.PrintOut To:=5, Preview:=True

Pattern 4: Print Only Specific Cell Ranges

Use this when you want to extract and print only a part of a sheet. This achieves the same effect as manually setting the “Print Area” in Excel.

Sub PrintSpecificRanges()
    ' Print only the specified multiple cell ranges
    Range("B2:F10, B15:F25").PrintOut
End Sub

Key Points

The .PrintOut method can also be used on Range objects. If you want to print multiple non-adjacent ranges, pass a string with addresses separated by commas (e.g., “Range1, Range2”) to the Range object.

Summary

The VBA .PrintOut method is a very flexible feature. You can control exactly what gets printed depending on which object (Worksheet, Workbook, Range, etc.) you use it with.