Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Shortcut to Move between Two Worksheets.
by Allen Wyatt
(last updated February 21, 2015)
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:
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9669) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Shortcut to Move between Two Worksheets.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
By default, Excel provides some feedback on your formulas so that you can easily locate potential errors. If you get tired of ...Discover More
When you open a workbook, Excel examines that workbook to make sure it can understand the data it contains. This can lead to ...Discover More
Jumping to the last cell in a worksheet should be easy, but you may not always get the results that you expect. This tip ...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 ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.