Excel shortcut: switching back and forth between that last active sheet and the current exit sheet

Question

I know the shortcut CRTL + page up/down but I need the similar function that exists eg in internet explorer: Shift + alt I found a macro that is close but does not entirely solve the problem:

If ActiveSheet.Name = "To Do" Then Sheets("Budget").Activate ElseIf ActiveSheet.Name = "Budget" Then Sheets("To Do").Activate

I can name the A1 cell with the sheet name and use search (f5) and type the name, but this is to cumbersome.

Any ideas, guys?

Solution 1

Dan's solution works. If you don't want to store the LastSheet in a cell range try:

 Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  ActiveWorkbook.Names.Add Name:="whchSheet", RefersToR1C1:=Sh.Name
 End Sub

still create a hotkey as he suggests

 Sub MoveToLastSheet()
  Sheets(Application.Evaluate(Names("whchSheet").Value)).Select
 End Sub

Solution 2

What I do: use View > Window > New Window to open the current workbook in a different application window. Use each window to display one of the sheets you're working with. You can now switch between them using the regular Alt+Tab (on Windows).

This method has another benefit: a workbook can be open in more than two windows, each displaying a different worksheet.

The (possible) disadvantage of this method is that you may need to hit Alt+Tab more than once if another application window has become active since you last looked at Excel.

However, you can also:

• Use the Task View to display all open windows and easily select one Win+Tab;
• If you work with a lot of different applications/documents, put your Excel windows in their own virtual desktop. While you're in that desktop, Alt+Tab will work nicely. You can switch to the 'previous' desktop using Win+Ctrl+Left, then do that again to return to the desktop containing the Excel windows. If I am working on projects, I typically put 'general apps' (browser, email, OneNote) in one virtual desktop, and have a different desktop for each project.
• If you have multiple Excel windows open, and they are grouped together in the Taskbar, you can cycle through them (in the order they were last active) by Ctrl+Clicking the grouped taskbar button.

Obviously, each method has different pros and cons, depending on how many applications and documents you have open, and how you want to interact with them.