Reference to new sheet after .Copy

Question

How can I reference to the new sheet after its copied as a new workbook.

Dim MainWb As Workbook: Set MainWb = ThisWorkbook
Dim SheetToCopy As Worksheet: Set SheetToCopy = MainWb.ActiveSheet

SheetToCopy.Copy

Is there a way to define a name to it? Someting like

 Set CopiedSheet = SheetToCopy.Copy

Previously: https://stackoverflow.com/questions/7692274/copy-sheet-and-get-resulting-sheet-object/7692456#7692456 — Tim Williams, May 02 '19 at 18:39

drec4s · Accepted Answer · 2019-05-02T19:09:20.867

0

When you copy a sheet it becomes the ActiveSheet, so you can reference that.

Dim MainWb As Workbook: Set MainWb = ThisWorkbook
Dim SheetToCopy As Worksheet: Set SheetToCopy = MainWb.ActiveSheet
Dim CopiedSheet As Worksheet

SheetToCopy.Copy 

Set CopiedSheet = ActiveWorkbook.ActiveSheet

edited May 02 '19 at 19:09

answered May 02 '19 at 18:27

drec4s

7,946
8
33
54

When I use `.Copy` it creates a new workbook I want to reference to the sheet after its copied to a new workbook. – luke May 02 '19 at 18:46
You need to reference the `activesheet` then. I've updated the answer. – drec4s May 02 '19 at 19:09

Reference to new sheet after .Copy

1 Answers1