Thread: Number of pages in the workbook

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

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

Good evening HarryX

Originally Posted by 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

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