Wauvel

Excel like a finance pro.

← All VBA snippets

Set a sheet up to print on one page wide

PageSetup in code, so every schedule prints the same way without anyone remembering.

1What it does

The most common bad PDF in finance is a schedule split across four pages with no column headings after the first. Three PageSetup properties fix it: `Zoom = False` (which is what lets FitToPages work at all), `FitToPagesWide = 1`, and `FitToPagesTall = False` so it runs as long as it needs instead of shrinking to nothing. Add `PrintTitleRows` and the headings repeat on every page. PageSetup is notoriously slow — each property talks to the printer driver — so set ScreenUpdating false and expect a beat.

2The code

Sub SetUpForPrint()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Application.ScreenUpdating = False

    With ws.PageSetup
        .Orientation = xlLandscape

        ' Zoom MUST be False or FitToPages is ignored entirely.
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False        ' False = as many pages tall as needed

        .PrintTitleRows = "$1:$1"      ' repeat the header on every page
        .PrintTitleColumns = "$A:$A"   ' and the account column

        .LeftMargin = Application.InchesToPoints(0.4)
        .RightMargin = Application.InchesToPoints(0.4)

        .CenterFooter = "&P of &N"                    ' page x of y
        .RightFooter = "Run &D"                       ' the run date
        .LeftFooter = ThisWorkbook.Name
    End With

    Application.ScreenUpdating = True
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Make a wide schedule readable on paper or as a PDF.
  • Apply the same print setup to every sheet by grouping them first.
  • Stamp the run date on anything that leaves the building.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Account | Amount
page 1
(no headings)
page 2
(no headings)
page 3
(no headings)
page 4

Toggle Zoom above, then run it. One of these two does nothing at all.

5Pitfalls

FitToPagesWide is set and nothing happens, because `Zoom` was left at its default.

Fix: `Zoom = False` first. This is the single most common PageSetup mistake.

`FitToPagesTall = 1` shrinks a 200-row schedule to unreadable 4pt type.

Fix: Set it to False, which means "as tall as it needs".

The macro takes several seconds per sheet and looks hung.

Fix: PageSetup genuinely is slow — each property round-trips to the printer driver. Set ScreenUpdating false and set only what you need.

PrintTitleRows errors with a plain `1:1`.

Fix: It needs the absolute form: `"$1:$1"`.

6No-code alternatives

  • Page Layout view by hand — Fine once. The macro is for applying the same setup to twelve sheets, or every month.
  • Grouping sheets first — Set it once by hand across grouped tabs — no code, same result, and it applies to all of them.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.