Find Printed Page Numbers

[kajohnson9]

I have a workbook with numerous sheets. The first sheet is a Table of Contents sheet. The Table of Contents points to each of the sheets in the workbook, typical Table of Contents function. Is there a way of determining the printed page number without having to include the page number in the sheet name? Some sheets print on multiple pages while other sheets only print on one page.
Thanks,
Keith Johnson

 

[portews]

Try this:

Sub findPageNo()
'https://www.mrexcel.com/board/threads/find-printed-page-numbers.1215772/
Dim i As Long
Dim Pgs As Long
Dim Start As Long
Start = 2
'start at the second worksheet through the last worksheet
For i = 2 To ThisWorkbook.Sheets.Count
    'count the page breaks, horizontal and vertical. For every vertical break, you'll have double the pages
    Pgs = Sheets(i).HPageBreaks.Count * (Sheets(i).VPageBreaks.Count + 1)
    'paste the sheet name into a cell on the index page (starting at B6 in this example) and the page range next to it. 
    Sheets(1).Cells(i + 4, 2) = Sheets(i).Name
    Sheets(1).Cells(i + 4, 3) = "'" & Start & " - " & Start + Pgs
    'set up for the next page
    Start = Start + Pgs + 1
Next
End Sub

 

[kajohnson9]

Try this:

Sub findPageNo()
'https://www.mrexcel.com/board/threads/find-printed-page-numbers.1215772/
Dim i As Long
Dim Pgs As Long
Dim Start As Long
Start = 2
'start at the second worksheet through the last worksheet
For i = 2 To ThisWorkbook.Sheets.Count
    'count the page breaks, horizontal and vertical. For every vertical break, you'll have double the pages
    Pgs = Sheets(i).HPageBreaks.Count * (Sheets(i).VPageBreaks.Count + 1)
    'paste the sheet name into a cell on the index page (starting at B6 in this example) and the page range next to it.
    Sheets(1).Cells(i + 4, 2) = Sheets(i).Name
    Sheets(1).Cells(i + 4, 3) = "'" & Start & " - " & Start + Pgs
    'set up for the next page
    Start = Start + Pgs + 1
Next
End Sub

 

[kajohnson9]

I can understand how this works but I get a Subscript Out of Range (Run-time error 9). I'll have to delve into this more.
Thanks for the ideas.
Keith