Thursday, March 29, 2012

Saving And Returning To A Location

These three macros are used to save a location and then return to that location later.   It is
useful when you need to change the Selection range during the execution of a macro, and then
return to the original Selection range when your macro is complete.

Public Sub SaveLocation(ReturnToLoc As Boolean)

Static WB As Workbook
Static WS As Worksheet
Static R As Range

If ReturnToLoc = False Then
    Set WB = ActiveWorkbook
    Set WS = ActiveSheet
    Set R = Selection
Else
    WB.Activate
    WS.Activate
    R.Select
End If

End Sub

To save the current location, call SetSaveLoc.

Public Sub SetSaveLoc()
    SaveLocation (False)
End Sub

To return to the saved location, call GetSaveLoc.

Public Sub GetSaveLoc()
    SaveLocation (True)
End Sub

No comments:

Post a Comment