How to Create Shortcut to Go Back to Previous Worksheet in Excel

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.

Create Shortcut to Go Back to Previous Worksheet 1

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

Create Shortcut to Go Back to Previous Worksheet 2

Step 4: On current workbook, click Insert->Module to insert a module. Then Module1 is created.

Create Shortcut to Go Back to Previous Worksheet 3

Step 5: Click Module1, then enter below code.

Create Shortcut to Go Back to Previous Worksheet 4

Public PreviousSheet As String
    Sub Select_PreviousSheet()
    Application.Sheets(PreviousSheet).Select
End Sub

Create Shortcut to Go Back to Previous Worksheet 5

Step 6: Return to worksheet window, click Developer->Macros, in the opening Macro window, verify that Select_PreviousSheet is already listed.

Create Shortcut to Go Back to Previous Worksheet 6

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.

Create Shortcut to Go Back to Previous Worksheet 7

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.

 

 

You might also like:

Sidebar