Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Shortcut to Move between Two Worksheets.
You can easily move between worksheets in a workbook by using Ctrl+Pg Up and Ctrl+Pg Down. What if you want to use a shortcut to move between two specific, non-neighboring worksheets, such as Sheet1 and Sheet4? In this case, it is best to use a macro to do the jumping around.
If desired, you could define two macros that would do the jumping. One macro would jump to Sheet1 and the other to Sheet4. These would be easy enough to create using the macro recorder, and you could assign a shortcut key to each of the macros.
If you are looking for a single shortcut that will toggle between the two worksheets, then you can use a macro such as this:
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
Sub JumpBetween1()
If ActiveSheet.Name = "Sheet1" Then
Worksheets("Sheet4").Activate
Else
Worksheets("Sheet1").Activate
End If
End Sub
The macro simply checks to see which worksheet is currently displayed. If it is Sheet1, then Sheet4 is displayed. In all other instances, Sheet1 is displayed. This is handy, but it means that if you currently have Sheet2 displayed, the shortcut will always display Sheet1. You might not want the macro to do anything unless either Sheet1 or Sheet4 is displayed. In that case, you should use this variation of the macro:
Sub JumpBetween2()
If ActiveSheet.Name = "Sheet1" Then
Sheets("Sheet4").Activate
ElseIf ActiveSheet.Name = "Sheet4" Then
Sheets("Sheet1").Activate
End If
End Sub
Note that the only difference between the two macros is that the latter variation uses ElseIf to check if Sheet4 is displayed. This means that if any worksheets other than Sheet1 or Sheet4 is displayed, the macro will do nothing.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3216) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Shortcut to Move between Two Worksheets.
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!
Need to sort your worksheets so that they appear in an order determined by the value of a cell on each worksheet? Using a ...
Discover MoreExcel makes copying worksheets (duplicating them) rather easy. However, you may want a worksheet copy that differs from ...
Discover MoreYour workbooks can contain many, many worksheets. Which of those worksheets are the largest, however? Here's some ideas ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-02-13 05:57:20
DEVENDRA SINGH TAK
how to move sheet 1 to sheet 2 and another shortcut
2015-06-08 12:46:38
Scott Renz
Thanks, Allen, I did not know about using Ctrl+Pg Up and Ctrl+Pg Down. I had been using the mouse to go to the next sheet--which becomes difficult once I get to the last tab showing on the screen.
2015-06-07 12:15:02
Willy Vanhaelen
@Ray Austin
In my comment of 26 Jan 2015 on Excel tip
http://excel.tips.net/T002216_Printing_a_Worksheet_List.html
I posted a macro that creates a list of all the sheets with a hyperlink to them.
2015-06-06 20:57:38
MWilson
Ray:
There are a couple of ExcelTips that address your question:
http://excel.tips.net/T002718_Jumping_to_Alphabetic_Worksheets.html?awt_l=HYeYl&awt_m=JANKASCynsQF4X
http://excel.tips.net/T002144_Picking_Worksheets_Quickly.html?awt_l=EYukV&awt_m=IjSBBzjNO1sx4X
But what I do for really big xls files with many worksheets is to create a table of contents in worksheet tab #1. There I list all of the worksheet tab names in one column, and create a hyperlink to each in the next column. (You can sort those names/links any way that is best for you).
Now whenever I want to jump to a worksheet I go to that first tab and click on the hyperlink.
2015-06-06 07:06:05
Ray Austin
I typically move around between 4 or 5 worksheets in a workbooks having 40+ sheets.
Is it practicable to have a routine similar to the "Go To" Cmd and reveals the full alphabetical worksheet list on the screen, then allows you to select with 1 click please ?
Thank you
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