1

I've recorded a quick macro which moves from my starting worksheet to a different worksheet, copies some cells and then goes back to the original worksheet to paste the contents of the copied cells. While recording the macro the worksheet had a certain name and I'm trying to understand how to change it so that the macro will return to whatever worksheet I was on when initiating the macro and not returning to a specifically named macro. This is what the code looks like:

Sheets("vlookup template").Select
Range("A1:K1").Select
Selection.Copy
Sheets("Sheet8").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("vlookup template").Select
Range("B2:K2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet8").Select
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B2:K11")
Range("B2:K11").Select
Selection.Copy

I want to change it so that instead of going to 'sheet8' it returns to the original sheet.

RubberDuck
  • 11,933
  • 4
  • 50
  • 95
Amos
  • 107
  • 1
  • 3
  • 11

1 Answers1

3
Dim homeSheet As WorkSheet
Set homeSheet = ActiveSheet

'.... Do stuff

homeSheet.Activate

Please see Avoid Select and Activate for more information.

Community
  • 1
  • 1
RubberDuck
  • 11,933
  • 4
  • 50
  • 95
  • by `homeSheet.Select` did you mean [`.Activate`](http://msdn.microsoft.com/en-us/library/office/ff838003%28v=office.15%29.aspx?ppud=4)? – Aprillion Jun 02 '14 at 10:24
  • Oops. Yes. I did. That's what I get for not testing it because it's simple. Updated answer. Thank you @deathApril. – RubberDuck Jun 02 '14 at 10:28