Adding Page Numbers To Footer

[Twisted_Squeegie]

I have a workbook that is a standard form that I use. I'm trying to get the right footer to print "Page X of Y" but here's the thing, the basic blank workbook has three sheets. The first is a boilerplate sheet, "BLANK", that is always hidden. The second is the actual sheet that the information is typed into. The third is a reference page that isn't printed. The second page is simply titled "Page 1". When I hit New Sheet my code copies "BLANK" and places it before the last sheet and names it Page 2, Page 3, etc.
Generally I only print one sheet at a time, either for a new sheet or correction on an existing. I have this code that works when printing a single page but if I have multiple pages selected it only works on one.
Print page 2 and "Page 2 of X" shows.
Print page 2 and 3, "Page 2 of X" shows but page 3 footer is blank.

Ideas?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

x = Worksheets.Count - 2
ActiveSheet.PageSetup.RightFooter = ActiveSheet.Name & " of " & x
ActiveSheet.PageSetup.LeftFooter = "&8Form 108 - Rev. B"
   
End Sub

 

[yky]

Try this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim sh As Worksheet
Dim x As Integer

x = Worksheets.Count - 2

For Each sh In ThisWorkbook.Windows(1).SelectedSheets

sh.PageSetup.RightFooter = sh.Name & " of " & x
sh.PageSetup.LeftFooter = "&8Form 108 - Rev. B"
   
Next

End Sub

 

[Twisted_Squeegie]

Works great! Thanks!