VBA PrintOut in Excel — Print Sheets, Ranges and Copies from a Macro

TL;DR — PrintOut sends pages to a printer. Call it on a sheet, a range, a group of sheets or a workbook, and use named arguments for what you need: Copies, From/To, Preview, Collate. It prints the pages your Page Setup defines, so fix the page first. While you are building the macro, print with Preview:=True. And if the target is a file rather than paper, do not print at all — export to PDF.

Sub PrintInvoices()
    With ThisWorkbook.Worksheets("Invoice")
        .PageSetup.PrintArea = "$A$1:$G$45"
        .PrintOut Copies:=2, Collate:=True, Preview:=False
    End With
End Sub

This is the paper end of a three-part cluster on getting output out of Excel. Page Setup decides what a page is. PrintOut, covered here, sends those pages to a printer. ExportAsFixedFormat sends the same pages to a PDF file. Because both outputs read from one page definition, most "my printout is wrong" problems are page problems, and they are fixed before this line ever runs.

What you'll learn

• What PrintOut actually prints, and where the page comes from
• The arguments worth knowing: copies, page ranges, preview, collate
• Printing a sheet, a range, several sheets, or a whole workbook
• Testing without wasting paper
• Choosing a printer with ActivePrinter and putting the old one back
• The judgment call: PrintOut for paper, export for files

The mental model: PrintOut prints pages, not cells

PrintOut does not know about your table. It knows about pages: the rectangles Excel cuts the sheet into using the print area, orientation, scale, margins and page breaks. It sends those pages to the printer, in order. So when a column lands alone on page 2, or a blank page comes out at the end, the cause is the page definition, not the print call:

• A column spills to a new page because the sheet is portrait at 100% scale.
• A blank page appears because a formatted cell far below the data stretches the used range.
• Half the report is missing because an old print area is still set.

Keep that split in mind and the rest is simple: Page Setup shapes the pages, PrintOut delivers them.

The PrintOut arguments that matter

PrintOut takes up to nine arguments. These are the ones you will use:

Always write them as named arguments. .PrintOut 1, 2, 3 compiles, but nobody can tell which number is the copy count.

A sheet, a range, several sheets, or the workbook

The object you call PrintOut on sets the scope:

Worksheets("Report").PrintOut                       ' one sheet
Worksheets("Report").Range("A1:F30").PrintOut       ' one range, ignoring the print area
Worksheets(Array("North", "South")).PrintOut        ' several sheets, one job
ThisWorkbook.PrintOut                               ' every visible sheet

Printing a set of sheets as one job matters for page numbering and for duplex printers: the sheets come out as one document, with page numbers that carry on across them if the footer uses &P. To print each sheet on its own, loop with For Each and call PrintOut inside the loop.

Test without wasting paper

A print macro under development is a paper shredder. Two habits stop that:

Worksheets("Report").PrintOut Preview:=True    ' shows preview; the user decides
Worksheets("Report").PrintPreview              ' preview only, never prints by itself

Preview:=True is the switch to flip when the macro is finished: the code stays the same, only that argument changes. Better still, check the layout by exporting to PDF while you work — it shows exactly the pages PrintOut would send, and costs nothing.

Choosing a printer, and putting the old one back

Application.ActivePrinter is the printer Excel uses. You can set it, but the name must match exactly, including the port Windows assigned — something like "Label Printer on Ne02:" — and that port can differ from one computer to the next. Setting it also changes the printer for everything the user prints afterwards. So save the current value, switch, print, and restore — even if the print fails:

Sub PrintOnLabelPrinter()
    Dim oldPrinter As String
    oldPrinter = Application.ActivePrinter
    On Error GoTo Restore
    Application.ActivePrinter = "Label Printer on Ne02:"
    Worksheets("Labels").PrintOut
Restore:
    Application.ActivePrinter = oldPrinter
    If Err.Number <> 0 Then MsgBox "Could not print: " & Err.Description
End Sub

To find the exact name, print once by hand to that printer and run Debug.Print Application.ActivePrinter in the Immediate window.

The judgment call: paper through PrintOut, files through export

PrintOut has PrintToFile and PrToFileName arguments, and you will find code that pairs them with the Microsoft Print to PDF printer to make a PDF. Avoid it. It depends on a printer name that varies by machine and Windows language, it can pop up a Save dialog that freezes an unattended macro, and it changes the active printer. ExportAsFixedFormat makes the same PDF with no printer, no dialog and no side effects. The rule: PrintOut is for paper; a file is an export. And whichever output you choose, its pages come from Page Setup — fix it there.

How ExcelMaster helps

Print macros go wrong in ways you only see on paper: the lonely column on page 2, the trailing blank page, the wrong printer, the twenty copies that were meant to be two. Each costs a reprint before anyone looks at the code.

ExcelMaster lets you describe the job — "print the invoice sheet twice, landscape, one page wide, on the label printer" — and writes the page setup and the PrintOut call together, with the printer saved and restored. You preview it as a PDF before a single sheet of paper moves.

Frequently asked questions

How do I print a sheet with VBA?

Call PrintOut on the worksheet: Worksheets("Report").PrintOut. Add named arguments for what you need, such as Copies:=2 or From:=1, To:=3. It prints the pages defined by the sheet's page setup, so set the print area and orientation first if the layout matters.

How do I print a specific range in VBA?

Call PrintOut on the range: Range("A1:F30").PrintOut. That prints only the range, whatever the sheet's print area is. For a range you print often, set PageSetup.PrintArea once and print the sheet instead.

How do I show print preview before printing?

Pass Preview:=True to PrintOut, which opens print preview and lets the user print or cancel. To show the preview without printing at all, call PrintPreview on the sheet. Use preview while you develop the macro so tests do not waste paper.

How do I print to a specific printer in VBA?

Set Application.ActivePrinter to the printer's exact name including its port, such as "Office Printer on Ne01:", then call PrintOut. Save the previous value first and restore it afterwards, because the setting stays in effect for everything the user prints next.

Can I use PrintOut to make a PDF?

You can, with the Microsoft Print to PDF printer and PrToFileName, but it is fragile: printer names differ between machines and a Save dialog can stall the macro. Use ExportAsFixedFormat Type:=xlTypePDF instead — it needs no printer and takes the file path as an argument.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-29.

Related guides: VBA Save as PDF · VBA Page Setup · VBA Used Range · VBA For Each · VBA Workbook BeforePrint · VBA Immediate Window · VBA On Error