by Allen Wyatt
(last updated April 5, 2014)
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.
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!
Excel lets you specify how it should behave when you press Enter. If you change this behavior, Excel assumes you want it ...Discover More
If you like to use the mouse in your worksheet navigation efforts, you'll want to pay attention to this tip. Here you ...Discover More
Two easy ways to select an entire column in Excel.Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.