[Excel VBA] How to Freely Configure Headers and Footers for Printing

When printing forms or reports created in Excel, headers and footers are essential for including information such as “Document Title,” “Date,” “Page Number,” and “Filename” on every page. Setting these manually can be time-consuming, but VBA allows you to automate all these standard settings.

In this article, I will explain in detail how to use the PageSetup object to set strings and dynamic information in headers and footers using VBA, along with a list of convenient formatting codes.

Basics of the PageSetup Object

Each Excel worksheet has a PageSetup object that manages all print-related settings (margins, print orientation, scaling, etc.). Header and footer settings are configured using the following six properties of this object:

Section	Property	Location
Header	.LeftHeader	Top Left
	.CenterHeader	Top Center
	.RightHeader	Top Right
Footer	.LeftFooter	Bottom Left
	.CenterFooter	Bottom Center
	.RightFooter	Bottom Right

You set strings or special codes to these six areas respectively.

Sample Code for Header/Footer Settings

First, here is the basic VBA code to actually set headers and footers using these properties.

Sub SetHeaderAndFooter()

    ' Manipulate the page setup of the currently active sheet
    With ActiveSheet.PageSetup
    
        ' --- Header Settings ---
        ' Left: Set nothing (Empty)
        .LeftHeader = ""
        ' Center: Set document title in Bold
        .CenterHeader = "&BMonthly Business Report"
        ' Right: Current Date and Time
        .RightHeader = "&D &T"
        
        ' --- Footer Settings ---
        ' Left: Filename
        .LeftFooter = "&F"
        ' Center: Page Number (e.g., 1 / 3)
        .CenterFooter = "&P / &N Pages"
        ' Right: Worksheet Name
        .RightFooter = "&A"
        
    End With
    
    MsgBox "Header and footer setup is complete."
    
    ' Check the settings in Print Preview
    ActiveSheet.PrintPreview

End Sub

List of Formatting Codes for Headers & Footers

By embedding special formatting codes starting with & (ampersand) into the header or footer strings, you can apply various dynamic information and formatting.

Text Formatting

Code	Description
&B	Toggles Bold on/off.
&I	Toggles Italic on/off.
&U	Toggles Single Underline on/off.
&S	Toggles Strikethrough on/off.
&"<Font Name>"	Specifies the font name. (e.g., &"Arial")
&<Number>	Specifies font size in points. (e.g., &14)
&&	Displays the & character itself.

Date & Time

Code	Description
&D	Inserts the current date.
&T	Inserts the current time.

File & Sheet Information

Code	Description
&F	Inserts the filename (without path).
&A	Inserts the worksheet name.
&Z	Inserts the full file path and filename.

Page Numbers

Code	Description
&P	Inserts the current page number.
&N	Inserts the total number of pages.
&P+<Number>	Displays the current page number plus the specified number.
&P-<Number>	Displays the current page number minus the specified number.

Specific Configuration Examples Using Codes

By combining the codes above, more complex formatting is possible.

' Example 1: Filename in Bold, Sheet Name in Standard
' &B turns Bold ON, the next &B turns Bold OFF
.LeftHeader = "&B&F&B / &A"

' Example 2: Title in MS Gothic, 14pt
.CenterHeader = "&""MS Gothic""&14Important Report"

' Example 3: Display current page number plus 10
.RightFooter = "Control Number: &P+10"

Summary

To automatically configure print headers and footers in Excel VBA, it is important to understand the following points:

• Target the ActiveSheet.PageSetup object.
• Assign strings to the 6 properties (such as .LeftHeader).
• Use formatting codes starting with & for dynamic information like dates and page numbers.

Mastering these codes allows you to instantly reproduce professional, unified print settings that are tedious to configure manually.