How to apply different headers or footers on each page on an Excel spreadsheet?
By default, when inserting header or footer into a worksheet, the header or footer content will be applied to all pages. For many people, different pages may have different contents in their worksheets. And they tend to distinguish these pages with the header or footer content after printing out the worksheet. How to apply different headers or footers on each page on worksheet? Method in this article can help you.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
The following VBA code can help you to apply different headers or footers on each page of a worksheet. Please do as follows.
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. See screenshot:
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
1. In the VBA code, you need to assign different header or footer names in the piece of code “vLeft = Array("First page", "Second page", "Third page", "fourth page")”.
2. And specify the print area of each page in current worksheet in the piece of code “xRg = Array("A1:C50", "A51:C100", "A101:C150", "A151:C200")”.
3. For applying different footers, please replace the word “Header” in the code with “Footer”.
3. Press the F5 key to run the code. Then the Preview window pops up, please click the Print button to start printing the pages with different headers or footers.
Note: If there are 4 specified printed pages in worksheet, you need to click the Print button four times to finish the whole printing.