Code for page number

[LearnMeExcel]

hi all
i have 14 sheets in my workbook
i want to make code to put page number for every sheets in WB
when i use Footer the sheets Number reset from sheet to sheet
i want to start from first sheet and end in last sheet in WB
some worksheet in my wb more than one page

thanx in advanced

 

[Ralajer]

Try either of the below they loops through all the sheets and sets the first page to the count of the previous sheets' pages. I found two methods to count the number of page that will be printed reading this ozgrid page (link). The two subs below differ only on how the pages are counted.

The first has to activate the worksheet to get a count but it works better for non rectangular print ranges.

Sub SetFirstPageNumber()
    Dim ws As Worksheet
    Dim shtPgs As Integer
    Dim shtFirstPg As Integer
    
    shtFirstPg = 1
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.PageSetup.FirstPageNumber = shtFirstPg
        ws.Activate
        shtPgs = ExecuteExcel4Macro("Get.Document(50)")
        shtFirstPg = shtFirstPg + shtPgs
    Next ws
End Sub

This doesn't have to activate all the sheets but the print ranges have to be rectangular i.e. 2x3 4x6.

Sub SetFirstPageNumber2()
    Dim ws As Worksheet
    
    Dim shtPgs As Integer
    Dim shtFirstPg As Integer
    
    shtFirstPg = 1
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.PageSetup.FirstPageNumber = shtFirstPg
        '// Count the number of page breaks to determine the number of sheets
        shtPgs = (ws.HPageBreaks.Count + 1) * (ws.VPageBreaks.Count + 1)
        shtFirstPg = shtFirstPg + shtPgs
    Next ws
End Sub

EDIT:
This works with the &[Page] in the footer