[Excel VBA] How to Customize Headers and Footers for Printing

Introduction

When printing reports created in Excel, headers and footers are essential for displaying information such as the “Document Title,” “Date,” “Page Number,” and “File Name” on every page. Setting these manually can be time-consuming, but VBA can automate these standard settings completely.

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

Basics of the PageSetup Object

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

Header (Top of page)

• .LeftHeader
• .CenterHeader
• .RightHeader

Footer (Bottom of page)

• .LeftFooter
• .CenterFooter
• .RightFooter

You can assign text or special codes to each of these six areas.

Sample Code to Set Headers and Footers

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: File Name
        .LeftFooter = "&F"
        ' Center: Page Number (e.g., 1 / 3)
        .CenterFooter = "&P / &N Pages"
        ' Right: Sheet Name
        .RightFooter = "&A"
        
    End With
    
    MsgBox "Header and Footer settings are complete."
    
    ' Check the settings in Print Preview
    ActiveSheet.PrintPreview

End Sub

List of Formatting Codes for Headers and Footers

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

Specific Examples Using Codes

By combining the codes above, you can create more complex formats.

' Example 1: File name in Bold, Sheet name in Normal font
' &B turns Bold ON, the next &B turns Bold OFF
.LeftHeader = "&B&F&B / &A"

' Example 2: Title in Arial font, size 14
.CenterHeader = "&""Arial""&14Important Report"

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

Summary

To automatically set printing headers and footers in Excel VBA, keep the following points in mind:

1. Target the ActiveSheet.PageSetup object.
2. Assign strings to the six properties (such as .LeftHeader).
3. 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 would otherwise be tedious to configure manually.