Copy Worksheet from Template & Assign to Variable 1

[DaveInIowa]

Is there a more straightforward method than the code I have listed below to create a new worksheet from an existing template worksheet and then assign the new worksheet to a variable?

Public Sub CopyWorksheetFromTemplate()
    Dim NewWorksheet As Worksheet
    Dim Template As Worksheet
    
    Set Template = ThisWorkbook.Worksheets("Template")
    
    Template.Copy , Template
    Set NewWorksheet = ThisWorkbook.Worksheets("Template (2)")
    NewWorksheet.Name = "New WS Name"
    ' Code to fill NewWorkheet with data ...
    
End Sub

I don't like the fact that I'm relying on the newly created worksheet to have the template's name appended with " (2)". It seems as though there should be a way to assign the variable directly from the .Copy method.

 

[rmikesmith]

Try this:

Set NewWorksheet = ThisWorkbook.ActiveSheet

Regards,
Mike

 

[DaveInIowa]

Thanks Mike. So, I can always count on the .Copy method to make the newly created sheet the ActiveSheet? I have a tendency to shy away from using "Active" objects (ActiveSheet, ActiveCell, etc.) but this will work great if I can count on the behavior being consistent.

 

[rmikesmith]

AFAIK, the copied worksheet will always become the ActiveSheet. Just as if you had carried this out from the UI (i.e., Edit|Move or Copy Sheet...).

Mike