How to Apply Different Headers or Footers on Each Page on an Excel Spreadsheet?

Mostly, we use header and footer in MS Word, but have you ever tried using the header and footer for work sheets in Excel? This process will divide the infinite Excel sheets into pages and add headers and footers. However, when we apply a header and footer to the first sheet, the same header and footer are added to all the pages in the worksheet, but sometimes you want to add different headers and footers on each process. This tutorial will help you solve this problem, i.e., help you understand how we can apply different headers and footers on each page on an Excel sheet.

Applying Different Headers or Footers on Each Page in Excel

Here we will first create a VBA module and then attach it to a shape. Let us see a simple process to understand how we can apply different headers or footers on each page on an Excel spreadsheet.

Step 1

Let us consider a new Excel sheet. Now right-click on the sheet name and select view code to open the vba application, then click on insert, select module, and enter the programme as shown in the below image.

Program

Sub DifferentHeaderFooter()
'Updated By Nirmal
   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

Step 2

Now, save the sheet with macros enabled and exit the application by pressing Alt + Q. Draw a rectangle shape, right-click on it, and select "Assign a macro." A pop-up window will appear, as shown in the below image.

Then click on the button, and a different header will be applied to the sheets in the print preview mode. Now, to add different footers, replace the work header with a footer in the VBA code.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can apply different headers and footers on each page of an Excel spreadsheet to highlight particular sets of data.