Copying of sheets

  • Thread starter Daniel Bonallack
  • Start date
D

Daniel Bonallack

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
 
R

Rob Bovey

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 *
 
D

Daniel Bonallack

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
Click to expand...
("Sheet1", "Sheet2", "Sheet3", "Sheet4", _
 
Top