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)
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.
If you have installed Kutools for Excel, you can apply its Switch between last and current sheet utility to go back to last sheet with ease in Excel.
Just click the button in the Navigation Pane or press the Win + A keys simultaneously to switch between current sheet and last/previous one.
Note: After installing the Kutools for Excel, the Navigation Pane turns on by default. If you can’t find out the Navigation Pane, or the button in the Navigation Pane, you can click the Kutools > Navigation (or View > Navigation) to activate the Navigation pane, and then click the button in the far left of Navigation Pane.
Best Office Productivity Tools
Easy to Uninstall Completely | Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Spreadsheets: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!