Excel 365 - Header, Footer, and Print area

Copper Contributor

I have an Excel workbook with 20 tabs.  

Each tab has a different header except the date after the description of the particular sheet.

I need every sheet to have a footer with a page number starting with page 2, being numbered 2.


The issues - When I group all the sheets and change the Headers date - all of them will change at one time (which is good).

However, It makes all the footer numbers to be whatever page I am on, that number.  In addition, it changes the print setup.


I have tried to go in and change the header and footer all at one time, but then when I get to the footer it will delete the header.  


IT's driving me insane.  I am not a novice...but just can't figure this out.

6 Replies


Run this macro:

Sub SetPageNumbers()
    Dim wsh As Worksheet
    Application.PrintCommunication = False
    For Each wsh In Worksheets
        With wsh.PageSetup
            .FirstPageNumber = 2
            .CenterFooter = "&P"
        End With
    Next wsh
    Application.PrintCommunication = True
End Sub

@Hans Vogelaar 

Thank you, but that did not work.


In what way did it fail?

There is only one page per each tab (20 tabs) The code you gave me is not keeping the Print area that I had set up for each tab. Since it now has a different print area per tab, it is causing each tab to have multiple sheets and creating a number for those sheets.

Plus it removed my customized header on all the sheets. I have a different header on every tab, and only the date needs changing every month.


Make sure that you haven't selected multiple sheets when you run the macro.

I have tested the macro; it does not change the page headers of the individual sheets, nor the print area.

It only sets the page number in the page footer.

@Hans Vogelaar 


I got it to work.


Thank you for your help