How to go back to the original active cell after moving around in VBA?

Question

The vba code below:

Sub try_1()
Dim sac As Range  'Original ActiveCell
Set sac = ActiveCell
ActiveCell.Offset(0, 2).Select
sac.RefersToRange.Select
End Sub

Is intended to go back to the original ActiveCell, after doing stuff that is symbolized by the 2nd executable statement. However, it fails at the last statement just before "End Sub". Although there is an error message, I don't understand it. Can you help? I've tried leaving out "RefersToRange" but it did not work. I did figure a work around, but it is more complicated, and I'd prefer to keep the code as simple as reasonably feasible.

You may want to see [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) - perhaps the intermediate steps don't need to change the `ActiveCell` at all. — BigBen, Oct 05 '21 at 13:54

score 0 · Answer 1 · answered Oct 07 '21 at 06:46

You only need to store a reference to the ActiveCell at the beginning. Thts reference includes references to the Workbook and Worksheet

Dim OriginalActiveCell as Range

Set OriginalActiveCell  = ActiveCell

'... do stuff

' Restore where you were
OriginalActiveCell.Worksheet.Parent.Activate ' Workbook
OriginalActiveCell.Worksheet.Activate        ' Worksheet
OriginalActiveCell.Select                    ' Cell

score -1 · Answer 2 · edited Oct 07 '21 at 06:43

Thank you for your answers. Using sac.Select does work in the above example, but not if the active sheet is changed as well. A work-around that also works when the active sheet is changed is:

   oacSN = ActiveCell.Worksheet.Name
   oacRef = ActiveCell.Address
   '... code that changes active cell & active sheet
   Sheets(oacSN).Select
   Range(oacRef).Select  'goes back to earlier active cell

How to go back to the original active cell after moving around in VBA?

2 Answers2