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?

Answer 1

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.

Answer 2

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

Answer 3

If you would like to add a hotkey to switch to your previous sheet, first create a cell called "LastSheet" somewhere to store the value of the last sheet. Then place this in your ThisWorkbook:

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

Now, in Module1 place this code:

Sub MoveToLastSheet()
Sheets(Range("LastSheet").Value).Select
End Sub

Create a hotkey for the MoveToLastSheet Macro, and you are done. This will now take you to the previous sheet you visited.