Increment of page number in sheet and different sheet.

Hi Kar Fung,

Your screenshot makes it clear. If you want something like that, let's use small VBA funtion:

1. Press Alt + F11 and Choose: Insert > Module Then paste this code: Function PrintedPageNumber() As String

   Dim ws As Worksheet
   
   Dim i As Long
   
   Dim PageNo As Long
   
   Dim TotalPages As Long
   
   Dim HPages As Long
   
   Dim VPages As Long
   
   Application.Volatile
   
   'Current worksheet
   
   Set ws = Application.Caller.Worksheet
   
   'Calculate page breaks
   
   ws.DisplayPageBreaks = True
   
   HPages = ws.HPageBreaks.Count + 1
   
   VPages = ws.VPageBreaks.Count + 1
   
   'Pages before the current worksheet
   
   PageNo = 0
   
   For i = 1 To ws.Index - 1
   
       Worksheets(i).DisplayPageBreaks = True
   
       PageNo = PageNo + _
   
           (Worksheets(i).HPageBreaks.Count + 1) * _
   
           (Worksheets(i).VPageBreaks.Count + 1)
   
   Next i
   
   'Find the page containing the cell
   
   Dim r As Long
   
   Dim c As Long
   
   r = Application.Caller.Row
   
   c = Application.Caller.Column
   
   PageNo = PageNo + 1
   
   For i = 1 To ws.HPageBreaks.Count
   
       If ws.HPageBreaks(i).Location.Row <= r Then
   
           PageNo = PageNo + VPages
   
       Else
   
           Exit For
   
       End If
   
   Next i
   
   For i = 1 To ws.VPageBreaks.Count
   
       If ws.VPageBreaks(i).Location.Column <= c Then
   
           PageNo = PageNo + 1
   
       Else
   
           Exit For
   
       End If
   
   Next i
   
   'Total pages in the workbook
   
   TotalPages = 0
   
   For i = 1 To ThisWorkbook.Worksheets.Count
   
       With ThisWorkbook.Worksheets(i)
   
           .DisplayPageBreaks = True
   
           TotalPages = TotalPages + _
   
               (.HPageBreaks.Count + 1) * _
   
               (.VPageBreaks.Count + 1)
   
       End With
   
   Next i
   
   PrintedPageNumber = PageNo & " / " & TotalPages
   

   End Function
2. Put the formula in your cell. In the cell where your screenshot currently shows 2 / 5, enter: =PrintedPageNumber()
   It should display something like: 1 / 5 Then, when the cell is on the next printed page, it will show: 2 / 5 and when you use the same formula in the corresponding cell on another worksheet, the numbering continues rather than starting again at 1. One thing to check, because this is based on printed pages, make sure your sheets already have the correct: Page Layout > Size Page Layout > Orientation Page Layout > Margins Page Layout > Scale/Width/Height Print Area Otherwise Excel may calculate a different number of pages than you expect. Also, if you want the display to look exactly like your screenshot, you can simply use the code: ="Page: "&PrintedPageNumber() which will give: Page 2/5 If your workbook has exactly the same "Sheet: x / y" cell in the same location on every worksheet.

Regards,
Jhun

I wanted to put page in the cell as per picture above. The first number to be the particular page within the sheet and continue to increase at other sheet also. Then, the last number to be the total page. Kindly advise the formula.
Thanks.

Hi Kar Fung,

Good day. The A.I. suggestion above is an effective solution. However i'll just teach you the VBA as an alternative solution to your query which you can use as your future solution.

1. Press Alt + F11 to open the VBA editor, then:
2. Go to Insert > Module.
3. Paste this code:
   Sub SetContinuousPageNumbers()

   Dim ws As Worksheet
   
   Dim startPage As Long
   
   Dim pageCount As Long
   
   startPage = 1
   
   For Each ws In ThisWorkbook.Worksheets
   
       'Set the starting page number for this worksheet
   
       ws.PageSetup.FirstPageNumber = startPage
   
       'Count the pages this worksheet will print
   
       pageCount = ws.PageSetup.Pages.Count
   
       'Set the next worksheet's starting page
   
       startPage = startPage + pageCount
   
   Next ws
   
   MsgBox "Page numbering has been updated across all worksheets.", vbInformation
   

   End Sub
4. Close the VBA editor.
5. Press Alt + F8.
6. Select SetContinuousPageNumbers and click Run.

Before running it, make sure each worksheet already has the desired Print Area, orientation, paper size, and scaling. Those settings determine how many pages each sheet occupies.

Add the page number to the footer

1. For each worksheet, go to:
2. Insert > Header & Footer > Footer
3. Click the section where you want the number and choose Page Number.
4. You can use: Page &[Page] of &[Pages]
5. The result will look like: Sheet 1: Page 1 of 3, Page 2 of 3, Page 3 of 3 Sheet 2: Page 4 of 3, Page 5 of 3, Page 6 of 3 Sheet 3: Page 7 of 3, Page 8 of 3...

I hope this helps.

Regards,
Jhun