Home / Macros / Shortcut to Last Worksheet used

Shortcut to Last Worksheet used

The ExcelHelpDesk Support team received the following request for help

Problem – Shortcut to switch between Worksheets

Hello,
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 :)
Regards,
Zuzana

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

If this has been completed without any problems you will see the following in the VB editor for the workbook.

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

About Excel Help Desk

Check Also

CTRL+F1 to Hide the Excel Toolbar using VBA

There are a number of options to solve this problem, the simplest approach we found for this request was the combination of the "SendKeys" function and the Workbook_Activate procedure. Basically we created a routine that would Hide the Toolbar each time the workbook was activated. Then when the workbook was closed or de-activated it would reset the Toolbar to the original state.

4 comments

  1. I must be doing something wrong as this does not work for me in excel 2010.

    I have a 60 page scoring workbook and I need a “go back” button or short cut. To go back and forth between the scoring rate chart.

  2. Zuzana,

    The code should be as below, so it can be used with sheet names starting with a number

    Sheets(CStr(ThisWorkbook.Worksheets(“ChangeWorksheets”).Range(“ChangeWorksheetsPreviousWorksheetName”).Value)).Activate

    Petros
    http://www.spreadsheet1.com

  3. Hi,
    Thanks a lot for this! It’s really helfpul.
    Just a side note – some problem occurs, if the name of the sheet is a number – simple solution – use any letter in front of the number and it works again :)
    Zuzana