Get total number of pages to be printer.

[Tolure]

Hi everyone. I need a method or function that can count the TOTAL number of pages to that will be printed. I don't need anything with HPageBreaks or Get.document(50, ""sheet name"")") for these don't seem to work on my worksheet.

All they seem to do is give me a number lower then the number of pages that will be printed and they are dependent on where the active cell is.

Here is my code that does not work.

 Agreement_Page_Count = ExecuteExcel4Macro("Get.document(50,""Signature Page"")")

and

Function HowManyPagesBreaks(sSheet As Worksheet)
Dim iHpBreaks As Integer, iVBreaks As Integer
Dim iTotPages As Integer
    With sSheet
        .UsedRange.EntireRow.Hidden = True
        .UsedRange.EntireColumn.Hidden = True
        .Range(.PageSetup.PrintArea).EntireRow.Hidden = False
        .Range(.PageSetup.PrintArea).EntireColumn.Hidden = False
    End With
    'sSheet.Range("A2000").End(xlUp).Select
    iHpBreaks = sSheet.HPageBreaks.Count + 1
    iVBreaks = sSheet.VPageBreaks.Count + 1
    HowManyPagesBreaks = iHpBreaks * iVBreaks
    With sSheet
        .UsedRange.EntireRow.Hidden = False
        .UsedRange.EntireColumn.Hidden = False
    End With
    
End Function

Both codes return 2 when there are 3 pages that can be printed. Unless I pause the code select the cell at the bottom of my sheet (it ignores it if I use use a macro to select the bottom cell) and then continue my code.

I've tried it with and without the entirerow.hidden.

Is there a way of bypassing everything and using a marco to open up page preview and getting the total number of page to be printed?

If it helps I'm using excel 2010

 

[Tolure]

I found a solution that is slow as molasses. Could anyone help me find a way to speed up this solution. I have ~17 pages to go through line by line on one sheet and on the other I have ~3.

Function HowManyPagesBreaks(sSheet As Worksheet)
Dim iRowEnd As Integer
Dim iRow As Integer
Dim Page_Count As Integer
iRowEnd = sSheet.Range("A2000").End(xlUp).Row
Page_Count = 1
For iRow = 1 To iRowEnd
    If sSheet.Rows(iRow).PageBreak <> -4142 Then
        Page_Count = Page_Count + 1
    End If
Next
HowManyPagesBreaks = Page_Count
End Function