How to apply different headers or footers on each page on an Excel spreadsheet?
By default, when you add a header or footer to an Excel worksheet, the content is uniformly applied across every printed page. This standard behavior is convenient for consistency, but sometimes you may need each page to display unique information. For example, when printing financial reports, student lists, or forms with different sections, having a separate header or footer for each page can make the printout clearer and help distinguish sections. However, Excel’s native tools only allow you to set one header or footer for all pages, limiting flexibility without advanced methods. Let’s walk through how you can set unique headers or footers for each page in your Excel worksheet.
Apply different headers or footers on each page with VBA code
Apply different headers or footers on each page with VBA code
The solution below uses a VBA macro to assign designated headers or footers to each printed page. This method is useful if each section of your document (such as chapters, classes, or monthly reports) needs a unique identifier. While the process involves some initial setup, once configured, it allows for quick printing of various sections with distinct header/footer content.
1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Code window. If you're new to macros, modules are the sections where VBA code is stored for execution. Refer to the screenshot for guidance:
VBA code: Apply different headers on each page in Excel
Sub DifferentHeaderFooter()
Dim ws As Worksheet
Dim vLeft As Variant, vRight As Variant, xRg As Variant
Set ws = ActiveSheet
On Error Resume Next
vLeft = Array("First page", "Second page", "Third page", "fourth page")
xRg = Array("A1:C50", "A51:C100", "A101:C150", "A151:C200")
Application.ScreenUpdating = False
For i = 0 To UBound(vLeft)
With ws.PageSetup
.PrintArea = xRg(i)
.LeftHeader = vLeft(i)
End With
ws.PrintPreview
Next i
Application.ScreenUpdating = True
ws.PageSetup.PrintArea = ""
End Sub
Notes and parameter explanations:
3. Press the F5 key to run the macro. This will execute the code and bring up the Preview window. Here, click the Print button to start printing each defined section with its designated header or footer.
Note: If your worksheet has 4 specified print areas, you need to click the Print button four times—once for each section—to complete printing all pages with individual headers or footers. This is because the macro prints each section sequentially, so repeat the print action for each one.
In summary, while Excel does not natively support unique headers or footers for each page within a single worksheet, using VBA macros provides a practical and customizable solution. Always verify your settings and printed output, and consider alternative approaches for complex scenarios. If you frequently need to personalize printed pages, investing some effort in automating this with VBA can save significant manual work.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in