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

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.....

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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Ron de Bruin" wrote in message
...
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

I agree with you Jon

--

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


"Jon Peltier" wrote in message ...
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Ron de Bruin" wrote in message
...
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