VBA Excel - Header and Footer dynamic page number (sheets)

Question

I wish to have an excel document printed where each unhidden sheet has the associated page number such that when I print, the page number goes from 1 for the first sheet, 2 for second sheet and so on.

Note: I also have some hidden sheets hidden here and there such that the page number must be only dependent on unhidden sheets.

Although, using the code below, when I select all worksheets and print the active sheets, the page number doesn't change from one sheet to another but is equal to one for each sheet.

Any ideas on how to fix this???

Sub headersfooters()
 Dim index As Integer
 Dim ws As Worksheet
    index = wsVolumes.Range("k4")
    Select Case index
    Case Is = 1
    For Each ws In ActiveWorkbook.Worksheets
    ws.Visible
         ws.PageSetup.RightHeader = "French"
          ws.PageSetup.RightFooter = "&P"
    Next ws
End
    Case Else
        For Each ws In ActiveWorkbook.Worksheets
         ws.PageSetup.RightHeader = "English"
         ws.PageSetup.RightFooter = "&P"
      Next ws
    End
            End Select
End Sub

Thanks!!

Dy.Lee Dy.Lee · Accepted Answer · 2017-07-14T01:21:42

What think about using ws.PageSetup.FirstPageNumber?

Sub headersfooters()
 Dim index As Integer
 Dim ws As Worksheet
 Dim n As Integer
    index = wsVolumes.Range("k4")
    Select Case index
    Case Is = 1
        For Each ws In ActiveWorkbook.Worksheets
            ws.Visible
             ws.PageSetup.RightHeader = "French"
              ws.PageSetup.RightFooter = "&P"
              n = n + 1
              ws.PageSetup.FirstPageNumber = n
        Next ws

    Case Else
        For Each ws In ActiveWorkbook.Worksheets
         ws.PageSetup.RightHeader = "English"
         ws.PageSetup.RightFooter = "&P"
         n = n + 1
         ws.PageSetup.FirstPageNumber = n
        Next ws

    End Select
End Sub

VBA Excel - Header and Footer dynamic page number (sheets)

1 Answers