Number of pages in the workbook

[HarryX]

I have a workbook with 3 sheets. Each of these sheets can contain up to 5 pages. The formatting is different on each sheet.
My customer wants to see "Page x of y" written in a certain cell (not in the header - that would be too easy ) and y = number of pages in the workbook!
I found some scripts that can determine the number of pages in a sheet but none that can determine the number of pages in the whole workbook.
Anybody got any hint how to solve this problem?

Thanks, Harry

[6StringJazzer]

I found some scripts that can determine the number of pages in a sheet but none that can determine the number of pages in the whole workbook.

I could do this from scratch but it will be easier to start with what you already have. If you post the code that determines pages in a sheet I'll show you how to iterate through all the sheets and add up pages in the whole workbook.

[HarryX]

This worked best so far:

  Public Function PageNumber( _
                Optional ByRef rng As Excel.Range) As Variant
        Dim pbHorizontal As HPageBreak
        Dim pbVertical As VPageBreak
        Dim nHorizontalPageBreaks As Long
        Dim nVerticalPageBreaks As Long
        Dim nPageNumber As Long

        On Error GoTo ErrHandler
        Application.Volatile
        If rng Is Nothing Then _
            Set rng = Application.Caller
        With rng
            If .Parent.PageSetup.Order = xlDownThenOver Then
                nHorizontalPageBreaks = .Parent.HPageBreaks.Count + 1
                nVerticalPageBreaks = 1
            Else
                nHorizontalPageBreaks = 1
                nVerticalPageBreaks = .Parent.VPageBreaks.Count + 1
            End If
            nPageNumber = 1
            For Each pbHorizontal In .Parent.HPageBreaks
                If pbHorizontal.Location.Row > .Row Then Exit For
                nPageNumber = nPageNumber + nVerticalPageBreaks
            Next pbHorizontal
            For Each pbVertical In .Parent.VPageBreaks
                If pbVertical.Location.Column > .Column Then Exit For
                nPageNumber = nPageNumber + nHorizontalPageBreaks
            Next pbVertical
        End With
        PageNumber = nPageNumber
ResumeHere:
        Exit Function
ErrHandler:
        'Could use much more error handling...!
        PageNumber = CVErr(xlErrRef)
        Resume ResumeHere
    End Function

I found it here

[dominicb]

Good evening HarryX

I found some scripts that can determine the number of pages in a sheet but none that can determine the number of pages in the whole workbook.

John Walkenbach has a solution to this here on his old pages.

HTH

DominicB

[HarryX]

Thanks dominicb, looks like a good point to start from!