See this KB

Copying Worksheet Programmatically Causes Run-Time Error 1004
http://support.microsoft.com/default...84&Product=xlw


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message oups.com...
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