Macro for page numbering

[rwallage]

Hi, I get an Excel file from our accounting system, containing several spreadsheets. I have recorded a macro for the page setup to print it all, and it works, apart from page numbering. I run the macro, but when done, there is no page numbering.
This is part of the macro:

With ActiveSheet.PageSetup
.LeftHeader = "&BBalance Sheet&B" & Chr(10) & "Tel Aviv Hilton"
.CenterHeader = ""
.RightHeader = "&D" & Chr(10) & "renewallage" & Chr(10) & "Page "
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "&P"

The last line is supposed to give the page number. While recording the macro I saw that on screen it actually writes "&[Page]", so I tried that as well. Same result. The macro runs, but afterwards there is no numbering.

What am I doing wrong?

 

[gallen]

Works Fine for me. Does the page number not show on the print preview?

 

[rwallage]

Neither on the preview, nor the print itself. When I go into the Page Setup - Header/Footer the field is empty; as if the macro did not run.

 

[gallen]

Have you stepped through each line of code, making sure each one executes as expected?

 

[rwallage]

I have re-recorded the macro, and it now works for the first spreadsheet, but not for the next two!
Also, the system supplies a header, which in the macro I shortened. When running the macro, the header returns to the previous setting! Infuriating doesn't begin to describe it...

 

[gallen]

You will need to run the code for each sheet. The page setup is only for that specific worksheet. Something like this would set the same pagesetup details for each sheet in the workbook:

Private Sub SetupPages()
Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Worksheets
        sh.PageSetup.RightFooter = "&P"
    Next
    
End Sub

 

[Comfy]

Or select all the sheets first:

Sub addHeaderFooter()
Sheets.Select


With ActiveSheet.PageSetup
.LeftHeader = "&BBalance Sheet&B" & Chr(10) & "Tel Aviv Hilton"
.CenterHeader = ""
.RightHeader = "&D" & Chr(10) & "renewallage" & Chr(10) & "Page "
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "&P"
End With
Sheets(1).Select
End Sub