Batch Header/Footer: Set Up Print-Ready Headers on Every Sheet in One Pass

TL;DR: You type your firm name, client name, and tax year into three prompts. The macro applies them to every workpaper sheet as print headers, adds a “FOR INTERNAL USE ONLY” footer watermark, and skips cover pages, notes, and TOC tabs automatically. Open Print Preview on any sheet and it’s ready to go.

The Problem

The partner wants the full Henderson workpaper printed for the file. Thirty-five tabs. You open Print Preview on the first sheet. The header is empty. The footer says “Page 1” — that’s it. No firm name, no client identifier, no indication this is a tax workpaper and not a random spreadsheet someone emailed. You set up headers on Sched-A. Switch to Sched-E. Do it again. Sched-L. Again. By tab 12, you’ve typed your firm name eleven times and you still have twenty-three to go.

Worse — next week the partner hands it back with review notes. You update the workpapers, re-print, and realize you never set up the footers on the three sheets you added mid-review. Now the partner’s binder has mix-and-match headers — some sheets are labeled, some aren’t. It looks sloppy. It looks like nobody reviewed the file before printing. And it was completely avoidable.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A multi-sheet workbook — tax workpapers, trial balance, schedules, depreciation
No pre-existing headers or footers required (the macro overwrites what’s there)

Limitations:

Works on ThisWorkbook — the workbook containing the macro. Store in Personal Macro Workbook to run on any open file
Overwrites existing headers and footers — if a sheet already has custom headers, they’ll be replaced
Skips sheets by exact name match (case-insensitive) — sheets with similar names like “Notes — Q1” will NOT be skipped. Add them to the EXCLUDE list or rename them
Page setup settings (orientation, margins, scaling) are left unchanged — this macro only touches headers and footers
Print area is not modified — if a sheet’s print area is set to a subset of the used range, that setting persists

#The Macro

Option Explicit

Sub BatchHeaderFooter()
    ' ── Batch Header/Footer ───────────────────────────
    ' Applies standardized headers and footers to every
    ' qualifying sheet. Three InputBoxes collect firm
    ' name, client name, and tax year. Sheets listed in
    ' the EXCLUDE constant are skipped (case-insensitive
    ' match). Reports updated and skipped counts.
    '
    ' Header:  LEFT = firm, CENTER = client, RIGHT = year
    ' Footer:  LEFT = file path, CENTER = watermark,
    '          RIGHT = page X of Y
    ' ────────────────────────────────────────────────────

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

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim firmName As String, clientName As String, taxYear As String
    Dim updated As Long, skipped As Long
    Dim excludeArr() As String
    Dim i As Long
    Dim isExcluded As Boolean

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

    ' ── Step 1: Collect inputs ─────────────────────────
    firmName = InputBox("Firm name:", "Header Setup")
    If firmName = "" Then GoTo CleanUp

    clientName = InputBox("Client name:", "Header Setup")
    If clientName = "" Then GoTo CleanUp

    taxYear = InputBox("Tax year (e.g., FY2026):", "Header Setup")
    If taxYear = "" Then GoTo CleanUp

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

    ' ── Step 3: Apply to each qualifying sheet ─────────
    updated = 0
    skipped = 0

    For Each ws In ThisWorkbook.Worksheets
        ' Check exclusion list
        isExcluded = False
        For i = 0 To UBound(excludeArr)
            If StrComp(ws.Name, excludeArr(i), vbTextCompare) = 0 Then
                isExcluded = True
                Exit For
            End If
        Next i
        If isExcluded Then
            skipped = skipped + 1
            GoTo NextSheet
        End If

        ' Apply headers and footers
        With ws.PageSetup
            .LeftHeader = firmName
            .CenterHeader = clientName
            .RightHeader = taxYear
            .LeftFooter = "&Z"
            .CenterFooter = "FOR INTERNAL USE ONLY"
            .RightFooter = "&P of &N"
        End With

        updated = updated + 1

NextSheet:
    Next ws

    ' ── Step 4: Report results ─────────────────────────
    MsgBox updated & " sheet(s) updated." & vbCrLf & _
           skipped & " sheet(s) skipped.", _
           vbInformation, "Done"

CleanUp:
    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

#Three prompts, three escape hatches

The macro asks three questions and exits cleanly if you cancel any of them:

firmName = InputBox("Firm name:", "Header Setup")
If firmName = "" Then GoTo CleanUp

This is a deliberate UX choice. If the user clicks Cancel on the first InputBox — maybe they opened the wrong workbook, maybe they ran the wrong macro — the macro exits without touching anything. No partial headers. No “I accidentally ran this” panic. The GoTo CleanUp pattern ensures Excel’s state (screen updating, calculation mode) is restored even on early exit.

The second and third prompts have the same escape hatch. Cancel at any point and the macro bails without writing a single header. This is preferable to the alternative — collecting all three inputs at once — because it gives the user three distinct decision points rather than one big form they might rush through.

#The exclusion list exists so you don’t have to label dashboards as “Sched-Something”

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

Cover pages, table of contents sheets, engagement notes, and dashboards don’t need “FOR INTERNAL USE ONLY” stamped on them — they already communicate their purpose. The exclusion list keeps the macro from defacing them.

The list is a comma-separated string parsed at runtime with Split(). This makes it trivially editable — no array syntax, no line continuations, just a comma between sheet names. A preparer who’s never touched VBA can add their firm’s “Summary” or “Checklist” tab by typing a comma and the name.

The comparison uses StrComp with vbTextCompare for case-insensitive matching:

If StrComp(ws.Name, excludeArr(i), vbTextCompare) = 0 Then

This means "cover", "Cover", and "COVER" all match. No one has to remember how they capitalized a tab name six months ago.

#PageSetup is slow — but we’re not doing much with it

Excel’s PageSetup object is notoriously slow in VBA. Every property access (.Orientation, .Zoom, .FitToPagesWide) triggers a cross-process call to the print engine. That’s why the print-ready-all-sheets macro (a later post) shows a status bar progress indicator — it touches a dozen PageSetup properties per sheet.

This macro only sets six properties: three header strings and three footer strings. Six cross-process calls per sheet, 30 sheets = 180 calls. On a modern machine, that completes in under a second. No status bar needed. No progress indicator needed. The macro is fast because it’s intentionally narrow in scope.

If you add orientation, margins, and scaling settings to this macro, the execution time will grow noticeably. That’s a different macro — keep this one focused.

#The footer codes — `&Z`, `&P`, `&N`

Excel’s header/footer system uses ampersand codes that date back to Excel 5. They’re not documented in the Object Browser and they don’t appear in IntelliSense. Here’s what this macro uses:

Code	Meaning	What prints
`&Z`	Full file path	`C:\Engagements\Henderson\2026 TB.xlsx`
`&P`	Current page number	`1`, `2`, `3`…
`&N`	Total page count	`5`, `12`, `47`…
`&D`	Current date	`7/29/2026`
`&F`	Filename only	`2026 TB.xlsx`
`&T`	Current time	`3:45 PM`

The macro uses "&Z" for the left footer — the full file path — so anyone holding a printed workpaper can locate the source file later. "&P of &N" on the right gives standard page numbering. The center footer is plain text: "FOR INTERNAL USE ONLY" — a watermark-level disclaimer that appears on every printed page without needing to actually watermark the cell contents.

#Why the message box matters here more than for other macros

Headers and footers are invisible on screen. You run the macro, nothing appears to change in the grid, and you wonder if it worked. The message box is your only confirmation without opening Print Preview:

12 sheet(s) updated.
3 sheet(s) skipped.

If you expected 15 updated and got 12, you know to check the skipped tabs. Maybe a sheet was named “Notes — Q1” instead of “Notes” and didn’t match the exclusion list. Maybe you have a “Summary” tab that needs to be added to EXCLUDE. The message box catches these mismatches immediately.

#Save before you run — headers are VBA, there’s no undo

Like every macro on this blog, batch header/footer has no Ctrl+Z. But the risk is lower here than with macros that modify cell values — headers and footers can be cleared manually via Page Layout → Print Titles → Header/Footer → None. If you run the macro and hate the result, you can strip all headers in the same dialog you’d use to set them manually. Still, save the workbook first. One click, total safety net.

#Adapt It

Change the exclusion list. Add or remove sheet names:

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

Use filename instead of full path. Replace "&Z" with "&F" if you only want the filename (e.g., "2026 TB.xlsx") instead of the full network path:

.LeftFooter = "&F"

Add the print date to the footer. Replace the right footer code to include the date the file was printed:

.RightFooter = "&D   &P of &N"

This prints 7/29/2026 Page 1 of 5.

Change the watermark text. The center footer is plain text — replace "FOR INTERNAL USE ONLY" with anything your firm requires:

.CenterFooter = "DRAFT — For Discussion Purposes Only"
.CenterFooter = "CONFIDENTIAL — Attorney-Client Privilege"
.CenterFooter = "PREPARED BY: Smith & Associates, CPA"

Skip sheets by partial name match. If you want to skip any sheet that contains a word (not just exact match), swap StrComp for InStr:

If InStr(1, ws.Name, excludeArr(i), vbTextCompare) > 0 Then
    isExcluded = True
    Exit For
End If

Now "Notes — Q1" and "Archive Notes" are both skipped when "Notes" is in the EXCLUDE list. Use with caution — a broad EXCLUDE entry like "Sched" would skip every schedule tab.

Run on the active workbook instead. If you store this macro in your Personal Macro Workbook, replace ThisWorkbook with ActiveWorkbook:

For Each ws In ActiveWorkbook.Worksheets

If no workbook is open, the error handler catches it.

Prompt for the exclusion list at runtime. Replace the Const EXCLUDE with an InputBox so you can decide which sheets to skip each time:

Dim excludeInput As String
excludeInput = InputBox("Skip these sheets (comma-separated):", _
                        "Exclusions", "Cover,Notes,TOC")
If excludeInput = "" Then excludeInput = EXCLUDE  ' fallback to default
excludeArr = Split(excludeInput, ",")

This gives you a default but lets you override it per-workbook.

Assign a keyboard shortcut. In the Macro dialog (Alt+F8), select BatchHeaderFooter, click Options, and set a shortcut like Ctrl+Shift+H.

See also: Print-Ready All Sheets — set margins, orientation, and fit-to-width after you’ve added headers. For updating dates inside the workbook (not just headers), see Bulk Date Updater.

Download the batch header/footer sample workbook (15 sheets — 12 workpaper tabs get headers, 3 skipped)

xlsx 106 KB