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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Want to make fast work of moving from one worksheet to another? Here's how to do the task when you have a lot of ...Discover More
Need to select an entire row? Here are two really easy ways to make the selection.Discover More
Want a quick way to get to the last cell in your worksheet? This tip provides a handy shortcut and a potential "gotcha."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.