Different footers in each page?

[Ferny]

Hello all,

I have an excel spreadsheet with 20 worksheets and 2 to 4 pages in each of the worksheets; each page needs to have a specific footer.
I have been trying adding VB code into a macro to do the trick, but so far I have not been successful.
The code I have is looking for the value in cell "A1" and then adding +1 in each of the pages in the worksheet by using a loop (Next)

This is the code I have so far:


Public Sub Footer()
Cancel = True
Dim ws As Worksheet
Dim Footer As Variant
Dim Footer2 As Variant
Dim i As Long
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Visible Then ws.Activate
With Sheets("sheet1").PageSetup
Footer = Sheets("sheet1").Range("A1")
For i = 1 To .Pages.Count
Footer2 = Footer + i
.RightFooter = " "
.RightFooter = "&""Calibri""&9&O" & Footer2
Next i
End With
Next
End Sub

I will really appreciate and help, I have been working on this project for a few days and I am out of ideas.

Thank you,
Ferny

 

[pgc01]

Hi Ferny

I'm not sure I understand what you need.

It's good that you posted the code but you should also explain clearly what you need.

Looking at the code it seems you want what the excel already does by default, which is to number the pages consecutively. The only difference I see is that instead of of the page number of the first page being 1, the default, you'd like to start with another page number, that you'd read from A1.

Ex., if you have 3 pages in the worksheet and you print it, instead of the page numbers being 1, 2, 3, you read the value in A1, for ex. 5, and the number of the 3 pages would appear as 5, 6, 7.

Is this correct?

 

[Ferny]

Hello PGC,

The excel file gets converted into PDF and sent in this manner instead of printing it.
I am trying to force excel to read the value into cell A1 and used it for the 1st page of the worksheet. The subsequent pages should take the same value and add +1 to arrive into the following page number.
One of the reasons I am using VB is because the file contains some worksheets used as cover pages and index that should not have a page number in them and should not be counted as part of the "total pages".
The complete footer is something like: Page 1 of 45. (45 does not include the index and cover pages in it)
I hope I am explaining well enough, my brain is already tired of trying to figure this out..Ha!
I am also a beginner with VB coding and I have been reading some postings and articles in order to help me getting the results I need.

Thank you for caring and for responding to me, I appreciate any help!

Ferny

 

[pgc01]

If I understand correctly I'd just set the value for the first page in the worksheet and let excel do the rest.

Please try (you can check the result in the Print Preview), using the 45 that you posted (and that you can change):

Public Sub Footer()
Dim ws As Worksheet

Application.ScreenUpdating = False
Application.PrintCommunication = False

For Each ws In Worksheets
    If ws.Visible Then
        With ws.PageSetup
            .FirstPageNumber = ws.Range("A1").Value
            .RightFooter = "&""Calibri""&9&OPage &P of 45"
        End With
    End If
Next ws

Application.PrintCommunication = True
Application.ScreenUpdating = True
End Sub

 

[pgc01]

P. S.

What does the "&O" do?

 

[Ferny]

Thank you!!!!!!!
I appreciate so much the quick help.

FYI..I belive the &O is supposed to place the footer all the way to the right.

Ferny

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

FYI..I belive the &O is supposed to place the footer all the way to the right.

Thanks but I'm not sure it's like that. The code to right align characters is "&R". Anyway, the right footer is right aligned by default and so there's no need of any code to have it to the right.