Written by Allen Wyatt (last updated July 18, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Steve is looking for a way to determine, in a VBA macro, the number of pages that a worksheet will have, when printed, and the page number on which a particular cell will print. This task is not quite as easy as one would hope, but it can be done.
It seems that the best way to handle this is to use an outmoded (but still available) Excel 4 function to determine the number of total printed pages in a worksheet. Then you can use the HPageBreaks and VPageBreaks collections to figure out where the cell falls in the matrix of pages that will be printed. The following is an example of a macro that utilizes these items:
Sub PageInfo() Dim iPages As Integer Dim iCol As Integer Dim iCols As Integer Dim lRows As Long Dim lRow As Long Dim x As Long Dim y As Long Dim iPage As Integer iPages = ExecuteExcel4Macro("Get.Document(50)") With ActiveSheet y = ActiveCell.Column iCols = .VPageBreaks.Count x = 0 Do x = x + 1 Loop Until x = iCols _ Or y < .VPageBreaks(x).Location.Column iCol = x If y >= .VPageBreaks(x).Location.Column Then iCol = iCol + 1 End If y = ActiveCell.Row lRows = .HPageBreaks.Count x = 0 Do x = x + 1 Loop Until x = lRows _ Or y < .HPageBreaks(x).Location.Row lRow = x If y >= .HPageBreaks(x).Location.Row Then lRow = lRow + 1 End If If .PageSetup.Order = xlDownThenOver Then iPage = (iCol - 1) * (lRows + 1) + lRow Else iPage = (lRow - 1) * (iCols + 1) + iCol End If End With MsgBox "Cell " & ActiveCell.Address & _ " is on " & vbCrLf & "Page " & _ iPage & " of " & iPages & " pages" End Sub
One thing that you should keep in mind with this macro is that the HPageBreaks and VPageBreaks collections are only considered accurate if you are viewing the worksheet in Page Break Preview (View | Page Break Preview). Thus, you'll want to make sure that you are in that mode before selecting a cell and running the macro.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11581) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Page Numbers in VBA.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When creating macros, you often need to process numbers in various ways. VBA allows you to convert a numeric value to an ...
Discover MoreIf you have a series of values in a column, you might have a need to separate the values into even values and odd values. ...
Discover MoreExcel allows you to hide worksheets so that they aren't visible to those using your workbook. Hiding worksheets has a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments