CODEWITHSUNDEEP
vbaexcel
Joe.Net
Joe.Net

Reputation: 1215

Set a different Page footer for each Page in Excel

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?

Upvotes: 1

Views: 8214

Answers (2)

ThatJDoe
ThatJDoe

Reputation: 1

I know this is resurrecting a thread from the dead, but this is actually do-able in Excel VBA. I had to figure it out as I couldn't find an acceptable solution here, so I'm leaving this for the next poor sod:

The solution is to create a subroutine to set the header and footer, and then call that routine within a loop in a different sub, calling each worksheet one at a time and managing what you want to say by variable.

So something like this:

Sub InsertQuoteHeaderAndFooter(ByVal shtHeader As Worksheet, strText$)

    shtHeader.PageSetup.RightFooter = "&""Calibri"" &8 &K434643" & strTxt & " | &P of &N"

End Sub

Then call it in a For loop, modulating shtHeader and strText as needed. Hope this helps the next archaeologist.

Upvotes: -1

user2480047
user2480047

Reputation:

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.

Upvotes: 2

Related Questions