How to determine the number of Pinted Pages in Excel 2007

Excel 2007 - Tips-Determining The Number Of Printed Pages

If you need to determine the number of printed pages for a worksheet printout, you can use Excel's print preview feature, and view the page count displayed at the bottom of the screen.

This tip provides two ways to determine the number of printed pages -- one using the the Excel 4 (XLM) Get.Document macro function, the other using VBA.

Using an XLM macro in VBA

You can execute an XLM macro function from VBA, as follows:

PgCnt = ExecuteExcel4Macro("Get.Document(50)")

In the statement above, the number of printed pages in the active sheet is assigned to the PgCnt variable.

The VBA subroutine below loops through all worksheets in the active workbook and displays the total number of printed pages. Note that this may return incorrect results when using a user-specified print range.

Sub ShowPageCount()

PageCount = 0

For Each sht In Worksheets

sht.Activate

Pages = ExecuteExcel4Macro("Get.Document(50)")

PageCount = PageCount + Pages

Next sht

MsgBox "Total Pages = " & PageCount

End Sub

Using VBA

Stew Scott provided the VBA procedure below, which does not use XLM.

Sub NumberOfPrintedPages()

Worksheets(1).DisplayAutomaticPageBreaks = True

HorizBreaks = Worksheets(1).HPageBreaks.Count

HPages = HorizBreaks + 1

VertBreaks = Worksheets(1).VPageBreaks.Count

VPages = VertBreaks + 1

NumPages = HPages * VPages

Worksheets(1).DisplayAutomaticPageBreaks = False

MsgBox NumPages

End Sub