The ExcelHelpDesk Support team received the following request for help
Problem – Shortcut to switch between Worksheets
I would like to ask whether it is possible to define any shortcut (key sequence) in order to be able to quickly switch between last used sheets which are not next to each other – preferably using only left hand :).
Similarly, as it is for last windows used – there is the shorcut Alt+Tab.
Thanks a lot, it’ll make my life much more easier 🙂
For this problem we have developed specific code that when included to your workbook will provide the required functionality to switch between the current worksheet and the last worksheet accessed.
Step 1 – Create worksheet and Named reference in your Workbook
Open the workbook that you want this functionality included to and create a worksheet called “ChangeWorksheets”. Then in the cell A1 create a Named Reference with the value of “ChangeWorksheetsPreviousWorksheetName”. If you don’t know how to create a Named Reference simply click on cell A1 and then in the top left of the Excel window next to the formula bar type in the value for the reference. This value is used in the Macro’s to store the value of the Previous Worksheet Name.
Step 2 – Copy required code in your Workbook
Now we need to include the required code to your workbook that will drive the Shortcut to the Last Worksheet used. Open the VB Editor using Alt-F11. First we will include the routine that will store the Previous Worksheet. This routine is driven by the “Sheet Deactivate” event, every time a Sheet is de-activated the Sheet name for that worksheet is written to the worksheet “ChangeWorksheets” this can then be used to Shortcut back to the worksheet if needed. Now double click on ThisWorkbook and copy the following code into the Code window.
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) ThisWorkbook.Worksheets("ChangeWorksheets").Range("ChangeWorksheetsPreviousWorksheetName").Value = Sh.Name End Sub
Step 3 – Copy the code to Change Worksheets
We need another routine to change the worksheet when needed. This will be started by a shortcut key Ctrl-d. Lets start by creating a new module, right click on the “ChangeWorksheets” sheet object and select Insert -> Module. Now copy the following code into Module1 that was created.
Sub ChangeToPreviousWorksheet() Sheets(ThisWorkbook.Worksheets("ChangeWorksheets").Range("ChangeWorksheetsPreviousWorksheetName").Value).Select End Sub
Step 4 – Finally setup the Shortcut Key
Once the Code has been copied into the workbook we need to setup the Shortcut Key to initiate the macro. To do this close the VB Editor and click on the Macros button in the Developer tab. You should see the Macro “ChangeToPreviousWorksheet”, select that Macro and choose options. In that window set the Shortcut Key to Ctrl + d and click OK. This will store the Shortcut Key for that Macro and will start it each time it is selected.
Step 5 – Test the new Macro is working
Ok now that we have the code and other configuration done we can test that the Macro does what it is supposed to. Click on the “ChangeWorksheets” sheet and then on “Sheet2” or any other sheet. Then use Ctrl + d and you should be taken back to “ChangeWorksheets” sheet. If you are not taken back to the previous worksheet then one of the steps above must not have been followed correctly. Re-trace your steps and try again. If you still can’t get it to work download the Sample File below to see our worked example for this Macro.
Note: Once you have created the “ChangeWorksheets” sheet you can hide it from view as it is only needed to store the value of the last Worksheet visited.
|Download Sample File|
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment