Method 'Copy' of object'_Worksheet failed
 

Afternoon, new to developing so please go gently on the answers.

I have the following macro to create a number of sheets based on a list, name each sheet as per name on the list and then to copy paste a template onto each sheet. Sounds so (b)loody simple except for a little error message that pops up when its time to copy paste the
template:
Method 'Copy' of object'_Worksheet failed.

The macro looks like this:
Dim wstemp As Worksheet
Dim Rng As Range
Dim ListRng As Range
Set wstemp = Worksheets("Template") 'this is the one to copy
Set ListRng = Range(Range("B7"), Range("B7").End(xlDown))
For Each Rng In ListRng
If Rng.Text < "" Then
wstemp.Copy after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = Rng.Text End If Next Rng End Sub

And the debug is on this line
wstemp.Copy after:=Worksheets(Worksheets.Count)

So now to the world I ask for your assistance in sorting this little thorn in my side.

Thanks

Method 'Copy' of object'_Worksheet failed
 

Worked fine for me. Have you got another workbook open?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"q2w3e4r" wrote in message
...

Afternoon, new to developing so please go gently on the answers.

I have the following macro to create a number of sheets based on a
list, name each sheet as per name on the list and then to copy paste a
template onto each sheet. Sounds so (b)loody simple except for a little
error message that pops up when its time to copy paste the
template:
Method 'Copy' of object'_Worksheet failed.

The macro looks like this:
Dim wstemp As Worksheet
Dim Rng As Range
Dim ListRng As Range
Set wstemp = Worksheets("Template") 'this is the one to copy
Set ListRng = Range(Range("B7"), Range("B7").End(xlDown))
For Each Rng In ListRng
If Rng.Text < "" Then
wstemp.Copy after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = Rng.Text End If Next Rng End Sub

And the debug is on this line
wstemp.Copy after:=Worksheets(Worksheets.Count)

So now to the world I ask for your assistance in sorting this little
thorn in my side.

Thanks




--
q2w3e4r