Method 'Copy' of object'_Worksheet failed

[stephenh]

Afternoon,

I have th following macro to create a number 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 know to the world I ask for your assistance in sorting this little
thorn in my side.

Thanks

 

[Rawce]

Are any of the sheet names defined in your range over 32 characters
long or have any characters unacceptable to worksheet names? Probably
not the actual solution, but you never know.....

 

[Ron de Bruin]

See this KB

Copying Worksheet Programmatically Causes Run-Time Error 1004
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684&Product=xlw

 

[Jon Peltier]

I find the "Resolution" in this article to be cumbersome, but the
"Workaround" is fine. If I need anything from the sheet which I would have
copied, I use Copy & Paste Special.

- Jon

 

[Ron de Bruin]

I agree with you Jon