Excel – Set a different Page footer for each Page in Excel

excelvba

I have tested the sample code in the corresponding MSDN article:

Sub WorkWithPages() 
    ' Fill random data: 
    Range("A1", "R100").Formula = "=RANDBETWEEN(1, 100)" 

    Dim pgs As Pages 
    Set pgs = PageSetup.Pages 

    PageSetup.DifferentFirstPageHeaderFooter = True 

    ' Look in the Immediate window for this output: 
    Debug.Print "The current sheet can be printed on " & _ 
     pgs.Count & " page(s)." 

    Dim pg As Page 
    Set pg = pgs(1) 
    pg.CenterHeader.Text = "This is the first page's header" 

    Set pg = pgs(2) 
    pg.CenterFooter.Text = "This is the second page's footer" 

    Set pg = pgs(pgs.Count) 
    pg.CenterFooter.Text = "This is the last page's center footer." 
    pg.LeftHeader.Text = "This is the last page's header" 

    ' Note that Excel supports only distinct headers/footers 
    ' for the first page, so headers and footers on the second 
    ' and other pages are combined--the last value set overwrites 
    ' the header/footer. 

    ' See the values in the Immediate window. 
    ' Note that the code disregards errors that occur--attempting 
    ' to retrieve a header/footer setting that doesn't exist raises an error: 
    On Error Resume Next 
    Debug.Print "First page (CenterHeader) : " & pgs(1).CenterHeader.Text 
    Debug.Print "Second page (CenterHeader): " & pgs(2).CenterHeader.Text 
    Debug.Print "Second page (CenterFooter): " & pgs(2).CenterFooter.Text 
    Debug.Print "Third page (CenterFooter) : " & pgs(3).CenterFooter.Text 
    Debug.Print "Last page (LeftHeader)    : " & pgs(pgs.Count).LeftHeader.Text 
    Debug.Print "Last page (CenterFooter)  : " & pgs(pgs.Count).CenterFooter.Text 

    ' In conclusion, use the Page class to retrieve information about headers 
    ' and footers for specific pages. Use the PageSetup object to set the headers 
    ' and footers, as it's clearer to set them there. 
End Sub

But the values output by the Debug.Print "Second page (CenterFooter): " & pgs(2).CenterFooter.Text line differ from what is expected:

Second page (CenterFooter): This is the last page's center footer

Instead of the right: Second page (CenterFooter): This is the second page's footer.

I have tried different things but all the CenterFooter keep always the last input value. How can I change this behaviour such that each page gets the exact footer I want?

Best Solution

There are different footers/headers configurations but none of them allows to write a different value for each page. You can write a different text for the first page and for even/uneven pages; also you can add some formatting with certain variations page to page (e.g., page number) but this is it. In Word the rules are equivalent.

Regarding the MSDN code you provided, one of its comments says:

Note that Excel supports only distinct headers/footers for the first page, so headers and footers on the second and other pages are combined--the last value set overwrites the header/footer.

And thus this code is actually working as expected; although it is not too clear at first sight.

Related Question