Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Suppose that you have a large worksheet that requires 16 pages when printed out. You may wonder if there is a way, when working within the worksheet, to jump to some given page, such as page 5.
Word users know that they can, within Word, use the Go To dialog box to jump to various pages, but no such feature exists in Excel. There are a couple of ways you can approach the problem, however.
One approach is to select the cell that appears at the top of a page. (For instance, that cell that appears at the top-left of page 5.) You can then define a name for the cell, such as Page05. Do this for each page in your worksheet, and you can then use the features within Excel to jump to those names.
Another way you can do this is to use the page break preview mode. (To switch to page break preview, choose View | Page Break Preview.) You can then see where the page breaks are, select a cell on the page you want, and then return to normal view.
It is possible to also create a macro that will let you jump to a specific page, but it isn't as easy as you might think. The reason has to do with the possible use of hard page breaks, which can change where pages start and end. The following macro might do the trick for you, however. It prompts the user for a page number and then selects the top-left cell on the page entered.
Sub GotoPageBreak() Dim iPages As Integer Dim wks As Worksheet Dim iPage As Integer Dim iVertPgs As Integer Dim iHorPgs As Integer Dim iHP As Integer Dim iVP As Integer Dim iCol As Integer Dim lRow As Long Dim sPrtArea As String Dim sPrompt As String Dim sTitle As String Set wks = ActiveSheet iPages = ExecuteExcel4Macro("Get.Document(50)") iVertPgs = wks.VPageBreaks.Count + 1 iHorPgs = wks.HPageBreaks.Count + 1 sPrtArea = wks.PageSetup.PrintArea sPrompt = "Enter a page number (1 through " sPrompt = sPrompt & Trim(Str(iPages)) & ") " sTitle = "Enter Page Number" iPage = InputBox(Prompt:=sPrompt, Title:=sTitle) If wks.PageSetup.Order = xlDownThenOver Then iVP = Int((iPage - 1) / iHorPgs) iHP = ((iPage - 1) Mod iHorPgs) Else iHP = Int((iPage - 1) / iVertPgs) iVP = ((iPage - 1) Mod iVertPgs) End If If iVP = 0 Then If sPrtArea = "" Then iCol = 1 Else iCol = wks.Range(sPrtArea).Cells(1).Column End If Else iCol = wks.VPageBreaks(iVP).Location.Column End If If iHP = 0 Then If sPrtArea = "" Then lRow = 1 Else lRow = wks.Range(sPrtArea).Cells(1).Row End If Else lRow = wks.HPageBreaks(iHP).Location.Row End If wks.Cells(lRow, iCol).Select Set wks = Nothing End Sub
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5823) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!