How to Jump To a Specific Page in Excel
This is a great tip from Allen Wyatt’s Excel Tips
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 or, in Word 2007, display the View tab of the ribbon and click the Page Break Preview tool.) 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
Tip #5823 applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007
