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
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5823) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Excel lets you specify how it should behave when you press Enter. If you change this behavior, Excel assumes you want it ...
Discover MorePress the up or down arrow keys, and you expect Excel to change which cell is selected. If this doesn't occur on your ...
Discover MoreTwo easy ways to select an entire column in Excel.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2015-05-05 03:00:23
Ed
Hi. The Tip "Jumping To a Specific Page" (http://excel.tips.net/T005823_Jumping_To_a_Specific_Page.html) is basically what I'm trying to do, but I don't want to have to enter a page number. I just want it to automatically go to the top of the next page. Sorry, I'm a complete rookie with vba.
Is there any way to do this?
2015-04-20 10:12:23
Ray
I do thank you for your help and thoughts. The macro seems extensive and looks to be a bit more than I can handle, and being as I'm utilizing Office 2010 it might not apply. I think I'll just keep scrolling down until I can find some markers. My Excel file is fairly extensive as I'm dealing with 100 pages or more.
Thanks again:
Ray
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments