How to go back to previous/last sheet with shortcut in Excel?

Supposing there are dozens of worksheets in current workbook, and you want to go back to previous or last sheet to copy contents, or for other purposes. But now sheet names are congesting in the Sheet bar, and it’s hard to find out the previous sheet at a glance. So how to go back to previous/last sheet immediately with shortcut? Here are two tricks for you:

• Go back to previous/last sheet with VBA (8 steps)
• Go back to previous/last sheet by shortcut with Kutools for Excel (only one click)

Go back to previous/last sheet with VBA

The following VBA code enables you to switch between last used sheet and current sheet in current workbook. You can do as follows:

1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Double click the ThisWorkbook in the left bar, and then paste the following code into the opening window:

VBA 1: Go back to previous/last sheet

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
LastSheet = Sh.Name
End Sub

3. Keep selecting ThisWorkbook in the left bar, and then click Insert > Module, and paste following code into the opening Module window:

VBA 2: Go back to previous/last sheet

Public LastSheet As String
Sub Select_Last()
Application.Sheets(LastSheet).Select
End Sub

You can press F5 key or click the Run button to go back to the last used worksheet at once.

If you prefer to shortcuts to switch between current sheet and last used one, please go ahead:

4. Click Developer > Macros to open the Macro dialog box.

Note: By default the Developer tab does not display in Ribbon, click to know how to show/display developer tab in Excel Ribbon.

5. In the opening Macro dialog box, click to highlight the macro of Select Last, and then click the Options button.

6. In the Shortcut key section, specify shortcut to run this macro of Select Last, and click the OK button to close dialog box.

7. Close the Macro dialog box.

From now on, when you open several worksheets successively, you can press the specified shortcut keys (in our case press the Ctrl + b) to switch between current sheet and last/previous one.

Go back to previous/last sheet by shortcut with Kutools for Excel

Demo: one click to go back to previous/last sheet

Related articles:

Toggle / switch between two sheets back and forth

Navigate between windows in Workbooks