Mike Schaeffer's Blog

June 2, 2005

Lately, I've been finding myself spending lots of time toggling between two Excel spreadsheets to make edits. This little macro makes it easy in Excel 2000 to toggle between two spreadsheet windows. I reccomend you bind it to a keystroke.

 Option Explicit

Dim lastWindow As Variant

Sub HereAndThere() If IsEmpty(lastWindow) Then Set lastWindow = ActiveWindow Else Dim currentWindow As Window Set currentWindow = ActiveWindow

    Set lastWindow = currentWindow
End If

End Sub

Here's how you use it:

  • Run the macro once to save your current location.
  • Switch to your other spreadsheet
  • Now, running the macro will switch back and forth betweeen the two worksheets.

The "saving excursions" in the title is a reference to the save-excursion special form in Emacs Lisp. This macro isn't quite the same (and not nearly as powerful), but it reminded me of the Emacs feature. If it turns out to be useful, I might generalize my little macro to include some of the capabilities of Emacs' save-excursion.