Print-Ready All Sheets: One Click to Set Landscape, Narrow Margins, and Fit-to-Width on Every Tab

TL;DR: You’re printing a 30-tab workpaper for the partner’s binder. Every sheet needs landscape, fit-to-width, narrow margins, and a centered layout. This macro handles it all in one pass — prompts for visible-only or all-sheets, skips Cover and Notes tabs by name, shows live progress in the status bar, and tells you exactly how many sheets were set up. Open Print Preview on any tab and it’s ready.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A multi-sheet workbook — tax workpapers, trial balance, schedules, depreciation
• No special setup required — the macro handles sheets with or without existing settings

What the macro sets:

• Orientation: Landscape
• Scaling: Fit to 1 page wide (auto height)
• Margins: Narrow (0.25” left/right, 0.5” top/bottom)
• Alignment: Centered horizontally on the page
• Print area: Full UsedRange
• Headers/Footers: Assigned only if blank (does not overwrite existing headers)

Limitations:

• Works on ThisWorkbook — the workbook containing the macro. Store in Personal Macro Workbook to run on any open file
• Every qualifying sheet gets landscape — if you have a portrait-only sheet (e.g., Cover), add its name to the EXCLUDE constant
• Overwrites existing print settings — orientation, margins, scaling, print area are replaced, not merged
• Print settings in VBA are inherently slow — each PageSetup property access is a cross-process call. The status bar shows live progress so you know it’s working
• Does not set specific paper size (assumes Letter) or print titles (repeat rows/columns)

#The Macro

Option Explicit

Sub PrintReadyAllSheets()
    ' ── Print-Ready All Sheets ─────────────────────────
    ' Standardizes print settings across every qualifying
    ' sheet in one pass. Prompts for visibility scope
    ' (visible-only or all), skips excluded sheets by
    ' name, and reports live progress in the status bar.
    '
    ' Sets: landscape, fit 1 page wide, narrow margins,
    ' center-on-page, print area = UsedRange, and default
    ' headers/footers ONLY if the sheet doesn't have them.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const EXCLUDE As String = "Cover,Notes,TOC,Instructions,Dashboard"

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim updated As Long, skipped As Long, total As Long
    Dim excludeArr() As String
    Dim i As Long, j As Long
    Dim isExcluded As Boolean
    Dim visibleOnly As VbMsgBoxResult
    Dim progress As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Ask about visibility scope ─────────────
    visibleOnly = MsgBox("Set up visible sheets only?" & vbCrLf & _
        vbCrLf & "  Yes  = Visible sheets only" & vbCrLf & _
        "  No   = All sheets (including hidden)", _
        vbYesNoCancel + vbQuestion, "Print Setup Scope")

    If visibleOnly = vbCancel Then GoTo CleanUp

    ' ── Step 2: Parse exclude list ─────────────────────
    excludeArr = Split(EXCLUDE, ",")
    For i = LBound(excludeArr) To UBound(excludeArr)
        excludeArr(i) = Trim(excludeArr(i))
    Next i

    ' ── Step 3: Count qualifying sheets ────────────────
    total = 0
    For Each ws In ThisWorkbook.Worksheets
        If visibleOnly = vbNo Or ws.Visible = xlSheetVisible Then
            isExcluded = False
            For j = LBound(excludeArr) To UBound(excludeArr)
                If StrComp(ws.Name, excludeArr(j), vbTextCompare) = 0 Then
                    isExcluded = True
                    Exit For
                End If
            Next j
            If Not isExcluded Then total = total + 1
        End If
    Next ws

    If total = 0 Then
        MsgBox "No qualifying sheets found.", vbExclamation
        GoTo CleanUp
    End If

    ' ── Step 4: Confirm with user ──────────────────────
    If MsgBox("Apply print settings to " & total & " sheet(s)?" & _
        vbCrLf & vbCrLf & "This will overwrite existing page setup " & _
        "on every qualifying sheet.", vbYesNo + vbExclamation, _
        "Confirm") = vbNo Then GoTo CleanUp

    ' ── Step 5: Apply print settings ───────────────────
    updated = 0
    skipped = 0
    progress = 0

    For Each ws In ThisWorkbook.Worksheets
        ' Apply visibility filter
        If visibleOnly = vbYes And ws.Visible <> xlSheetVisible Then
            skipped = skipped + 1
            GoTo NextSheet
        End If

        ' Check exclusion list (case-insensitive)
        isExcluded = False
        For j = LBound(excludeArr) To UBound(excludeArr)
            If StrComp(ws.Name, excludeArr(j), vbTextCompare) = 0 Then
                isExcluded = True
                Exit For
            End If
        Next j
        If isExcluded Then
            skipped = skipped + 1
            GoTo NextSheet
        End If

        progress = progress + 1
        Application.StatusBar = "Setting up sheet " & progress & _
            " of " & total & ": " & ws.Name & "..."

        ' ── Page setup (group all calls for one sheet) ──
        With ws.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            .Orientation = xlLandscape
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.5)
            .CenterHorizontally = True
            .CenterVertically = False
            .PrintArea = ws.UsedRange.Address
        End With

        ' ── Headers/footers — only if blank ─────────────
        If ws.PageSetup.LeftHeader = "" And _
           ws.PageSetup.CenterHeader = "" And _
           ws.PageSetup.RightHeader = "" Then
            ws.PageSetup.LeftHeader = "&F"
            ws.PageSetup.RightHeader = "&D"
        End If

        If ws.PageSetup.LeftFooter = "" And _
           ws.PageSetup.CenterFooter = "" And _
           ws.PageSetup.RightFooter = "" Then
            ws.PageSetup.LeftFooter = ThisWorkbook.FullName
            ws.PageSetup.CenterFooter = "FOR INTERNAL USE ONLY"
            ws.PageSetup.RightFooter = "Page &P of &N"
        End If

        updated = updated + 1

NextSheet:
    Next ws

    ' ── Step 6: Summary ────────────────────────────────
    MsgBox updated & " sheet(s) set up for print." & vbCrLf & _
           skipped & " sheet(s) skipped.", vbInformation, _
           "Print Setup Complete"

CleanUp:
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Two passes: count first, apply second

The macro counts qualifying sheets before it touches anything. This serves two purposes. First, the confirmation message tells you exactly how many sheets will be affected — "Apply print settings to 24 sheet(s)?" — so you can cancel if the number is wrong. Second, it lets the status bar show meaningful progress ("Setting up sheet 12 of 24: Sched-E Deductions...") instead of a meaningless increment.

total = 0
For Each ws In ThisWorkbook.Worksheets
    If visibleOnly = vbNo Or ws.Visible = xlSheetVisible Then
        ' ... exclusion check ...
        If Not isExcluded Then total = total + 1
    End If
Next ws

The count pass is free — it iterates worksheet objects without touching any cells or page setup properties, so it completes in milliseconds.

#PageSetup is slow — group all calls for one sheet

Every ws.PageSetup property access is a cross-process call from VBA to Excel’s printing engine. Accessing five properties across five separate With blocks takes five cross-process calls. Wrapping them in a single With ws.PageSetup block minimizes the overhead.

With ws.PageSetup
    .Zoom = False
    .FitToPagesWide = 1
    .Orientation = xlLandscape
    .LeftMargin = Application.InchesToPoints(0.25)
    .RightMargin = Application.InchesToPoints(0.25)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.5)
    .CenterHorizontally = True
    .PrintArea = ws.UsedRange.Address
End With

Despite the grouping, setting up 30 sheets still takes 5–15 seconds depending on your machine and workbook size. That’s why the status bar feedback is non-negotiable — without it, the user sees nothing while Excel appears to freeze.

#Why Zoom = False matters

.Zoom = False
.FitToPagesWide = 1

Setting Zoom = False toggles Excel from percentage-based scaling to fit-to-page scaling. Without this line, FitToPagesWide = 1 has no effect — Excel ignores it and keeps whatever zoom percentage was already set. The order matters: Zoom must be set to False before FitToPagesWide.

Not setting FitToPagesTall means “automatic” — the sheet uses as many pages in height as it needs. For a workpaper with 50 rows that fits on one page in landscape, that’s one page total. For a depreciation schedule with 200 rows, the height expands to two or three pages automatically.

#Headers and footers are additive, not destructive

The macro checks whether headers or footers already exist before writing anything:

If ws.PageSetup.LeftHeader = "" And _
   ws.PageSetup.CenterHeader = "" And _
   ws.PageSetup.RightHeader = "" Then
    ws.PageSetup.LeftHeader = "&F"
    ws.PageSetup.RightHeader = "&D"
End If

If the sheet already has custom headers — perhaps applied by the Batch Header/Footer macro with firm name, client name, and tax year — this macro leaves them untouched. It only fills in blanks. The &F code inserts the workbook filename, &D inserts the current date, and the footer gets a file path, “FOR INTERNAL USE ONLY” watermark, and page numbers.

The same guard applies to footers. If any of the three footer sections is non-empty, the entire footer is left alone — the assumption being that if someone configured a footer, they configured all of it.

#The exclude list: Cover gets portrait everything

The EXCLUDE constant skips sheets that shouldn’t get landscape and narrow margins. A cover page is the textbook example — it’s one page with the firm logo, engagement title, and preparer info, and it looks terrible in landscape. The constant string is split by comma, trimmed, and matched case-insensitively.

Const EXCLUDE As String = "Cover,Notes,TOC,Instructions,Dashboard"

Add your own sheet names to this list before running. If your firm uses “Summary” as a cover sheet, add it. The matching uses StrComp with vbTextCompare so “COVER”, “cover”, and “Cover” are all treated identically.

#Why a MsgBox before anything happens

This macro overwrites print settings on every sheet it touches. There is no undo. The two-step confirmation — first the scope dialog (Yes/No/Cancel), then the explicit confirm with sheet count — gives you two chances to back out. If you selected “No” for all-sheets and the count says “24 sheet(s)” but you expected 12, you cancel, investigate, and run again.

#Adapt It