Copying of sheets
 

I have a macro that copies a template sheet to make new
ones.

It works fine, but after about 17 copied sheets, I get
the message "Copy Sheet method failed" or something like
that. I save, close Excel, and then begin the macro
again from where it got to. It goes for another 17, then
debugs again.

Is there some memory cache issue here, and if so, is
there a way of clearing it without closing Excel?

Thanks in advance
Daniel

Copying of sheets
 

Hi Daniel,

This error seems to be related to the number of copy operations
performed, not the number of sheets copied, so the best way I've found to
get around it is to copy sheets in groups of five or ten instead of one at a
time. The procedure below shows an example of doing it in groups of five.

Sub CopySheets()

Dim lCount As Long
Dim wksSource As Worksheet

Application.ScreenUpdating = False

Set wksSource = Worksheets("Sheet1")

''' Create the first five copies.
For lCount = 2 To 5
wksSource.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Sheet" & CStr(lCount)
Next lCount

''' Copy the rest of the sheets five at a time.
For lCount = 1 To 19
Worksheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", _
"Sheet5")).Copy after:=Worksheets(Worksheets.Count)
Next lCount

''' Rename all the sheets.
For lCount = 1 To Worksheets.Count
Worksheets(lCount).Name = "Sheet" & CStr(lCount)
Next lCount

Worksheets(1).Activate

Application.ScreenUpdating = True

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Daniel Bonallack" wrote in message
...
I have a macro that copies a template sheet to make new
ones.

It works fine, but after about 17 copied sheets, I get
the message "Copy Sheet method failed" or something like
that. I save, close Excel, and then begin the macro
again from where it got to. It goes for another 17, then
debugs again.

Is there some memory cache issue here, and if so, is
there a way of clearing it without closing Excel?

Thanks in advance
Daniel

Copying of sheets
 

Thanks Rob, I'll try out this solution.

Daniel

-----Original Message-----
Hi Daniel,

This error seems to be related to the number of copy
operations
performed, not the number of sheets copied, so the best
way I've found to
get around it is to copy sheets in groups of five or ten
instead of one at a
time. The procedure below shows an example of doing it
in groups of five.

Sub CopySheets()

Dim lCount As Long
Dim wksSource As Worksheet

Application.ScreenUpdating = False

Set wksSource = Worksheets("Sheet1")

''' Create the first five copies.
For lCount = 2 To 5
wksSource.Copy after:=Worksheets
(Worksheets.Count)
ActiveSheet.Name = "Sheet" & CStr(lCount)
Next lCount

''' Copy the rest of the sheets five at a time.
For lCount = 1 To 19
Worksheets(Array
("Sheet1", "Sheet2", "Sheet3", "Sheet4", _
"Sheet5")).Copy after:=Worksheets
(Worksheets.Count)
Next lCount

''' Rename all the sheets.
For lCount = 1 To Worksheets.Count
Worksheets(lCount).Name = "Sheet" & CStr(lCount)
Next lCount

Worksheets(1).Activate

Application.ScreenUpdating = True

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Daniel Bonallack" wrote in
message
...
I have a macro that copies a template sheet to make new
ones.

It works fine, but after about 17 copied sheets, I get
the message "Copy Sheet method failed" or something
like
that. I save, close Excel, and then begin the macro
again from where it got to. It goes for another 17,
then
debugs again.

Is there some memory cache issue here, and if so, is
there a way of clearing it without closing Excel?

Thanks in advance
Daniel


.

Copying of sheets
 

Hi Daniel / Rob

See this KB also

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rob Bovey" wrote in message ...
Hi Daniel,

This error seems to be related to the number of copy operations
performed, not the number of sheets copied, so the best way I've found to
get around it is to copy sheets in groups of five or ten instead of one at a
time. The procedure below shows an example of doing it in groups of five.

Sub CopySheets()

Dim lCount As Long
Dim wksSource As Worksheet

Application.ScreenUpdating = False

Set wksSource = Worksheets("Sheet1")

''' Create the first five copies.
For lCount = 2 To 5
wksSource.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Sheet" & CStr(lCount)
Next lCount

''' Copy the rest of the sheets five at a time.
For lCount = 1 To 19
Worksheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", _
"Sheet5")).Copy after:=Worksheets(Worksheets.Count)
Next lCount

''' Rename all the sheets.
For lCount = 1 To Worksheets.Count
Worksheets(lCount).Name = "Sheet" & CStr(lCount)
Next lCount

Worksheets(1).Activate

Application.ScreenUpdating = True

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Daniel Bonallack" wrote in message
...
I have a macro that copies a template sheet to make new
ones.

It works fine, but after about 17 copied sheets, I get
the message "Copy Sheet method failed" or something like
that. I save, close Excel, and then begin the macro
again from where it got to. It goes for another 17, then
debugs again.

Is there some memory cache issue here, and if so, is
there a way of clearing it without closing Excel?

Thanks in advance
Daniel