5

I have a macro that is duplicated on buttons on 3 worksheets which moves between worksheets and at the moment once the macro command is completed I have it set to return to the 3rd sheet every time. Is there a command I can put in the code that would return the view to the sheet that the macro was activated on in the first place rather than a set sheet.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Tom
  • 89
  • 1
  • 2
  • 10
  • 1
    Save the active sheet in a variable, then, after your macro finished, select the sheet within the variable again. – Mafii Feb 02 '17 at 10:57
  • 3
    My fist question would be, if you want to return to the initial worksheet, why are you leaving it? Maybe your macro could be written [without using Select nor Activate](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)? – Vincent G Feb 02 '17 at 10:57
  • @VincentG good point. Definitely the cleaner approach. – Mafii Feb 02 '17 at 10:59
  • I tend to use `with` in most of my codes but i'm finding this very complicated. It has to move rows of data to other worksheets depending on the contents of a few cells and works down the sheet in reverse order. – Tom Feb 02 '17 at 11:13

1 Answers1

8

Save the active sheet in a variable, then, after your macro finished, select the sheet within the variable again:

' at the start of your macro:
Dim sourceSheet as Worksheet
set sourceSheet = ActiveSheet


' at the end of your macro:
Call sourceSheet.Activate
Mafii
  • 7,227
  • 1
  • 35
  • 55