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
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
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks