Use a Worksheet Template in VBA

[Buyone]

Hello,

I've been using VBA created by Ron De Bruin (Sub Copy_To_Worksheets()), and have managed to amend it to my needs save for one part. I can't seem to be able to use a worksheet template?

This is the original code that works...

 Else
                'Add a new worksheet
                Set WSNew = Worksheets.Add(After:=Sheets(Sheets.Count))
                On Error Resume Next
                WSNew.Name = cell.Value
                If Err.Number > 0 Then
                    ErrNum = ErrNum + 1
                    WSNew.Name = "Error_" & Format(ErrNum, "0000")
                    Err.Clear
                End If
                On Error GoTo 0

But I'd like to use a template, inpace of a new worksheet. I've tried this, but no joy...
 Else
                'Add a new worksheet
                Set WSNew = Worksheets("Calcs").Copy(After:=Sheets(Sheets.Count))
                On Error Resume Next
                WSNew.Name = cell.Value
                If Err.Number > 0 Then
                    ErrNum = ErrNum + 1
                    WSNew.Name = "Error_" & Format(ErrNum, "0000")
                    Err.Clear
                End If
                On Error GoTo 0

I get a 424 runtime error on the above?

Thanks

 

[RoryA]

Copy doesn't return a worksheet object, so you have to Copy and then use the Activesheet:

Worksheets("Calcs").Copy After:=Sheets(Sheets.Count)
Set WSNew = Activesheet

 

[Buyone]

Rory, thanks very much for saving me hours of additional frustration!

All the very best.

Cheers