Usually we create multiple worksheets in excel and we can switch between these worksheets to do different operations. But if we want to quickly get back from current worksheet to the previous operating worksheet , how can we do? Dragging the scroll bar back to the previous worksheet is hard to find the proper worksheet due to sometimes we forget the name of the last worksheet, so it’s good to have a shortcut to go back to the previous worksheet. This article will introduce you how to create a shortcut key to go back to previous worksheet by VBA.
Create Shortcut Key to Go Back to Previous Worksheet by VBA
Step 1: Click Developer tab->Visual Basic or Alt+F11 to load Microsoft Visual Basic for Applications window.
Step 2: Double click on ThisWorkbook under VBAProject->Microsoft Excel Objects in the left bar.
Step 3: Enter below code into the window.
Private Sub Workbook_SheetDeactivate(ByVal Psh As Object) PreviousSheet = Psh.Name End Sub
Step 4: On current workbook, click Insert->Module to insert a module. Then Module1 is created.
Step 5: Click Module1, then enter below code.
Public PreviousSheet As String Sub Select_PreviousSheet() Application.Sheets(PreviousSheet).Select End Sub
Step 6: Return to worksheet window, click Developer->Macros, in the opening Macro window, verify that Select_PreviousSheet is already listed.
Step 7: Highlight ‘Select_PreviousSheet’, then click Options button. In Macro Options window, set Crtl + p as the shortcut key, enter the description as well. See screenshot below. Then click OK.
Step 8: Now you can switch between worksheets and check if the shortcut key is available. The shortcut key works well, it can help us return to previous worksheet quickly.