Memory Management

[Floyd Bates]

All:

I have a small VBA program that adds additions worksheets
to a workbook using the first sheet as a template for the
sheet being added.

At around 53 sheets it crashes and tell me "Copy method of
worksheet class failed".

Looking at this line: Debug.Print Application.MemoryUsed,
Sheets.Count

It fails at around 3 megs.

Once it fails I can save the workbook and rerun the code.
This time it will add approximately 20 more sheets before
I get the error above.

Here is the code for the copy commands:

Application.ScreenUpdating = False
Sheets(ActiveSheet.Name).Select
Sheets("Day 1").Copy After:=Sheets(Sheets.Count)
Application.CutCopyMode = False 'Clear Clipboard
Debug.Print Application.MemoryUsed, Sheets.Count
Sheets(ActiveSheet.Name).Name = "Day " & Sheets.Count

Is there a way around the error? Can I clear Excel's
memory somehow while the code is running?

Thanks in advance.

Floyd Bates

 

[Rob Bovey]

Hi Floyd,

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("Day 1")

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

''' Copy the rest of the sheets five at a time.
For lCount = 1 To 19
Worksheets(Array("Day 1", "Day 2", "Day 3", "Day 4", _
"Day 5")).Copy after:=Worksheets(Worksheets.Count)
Next lCount

''' Rename all the sheets.
For lCount = 1 To Worksheets.Count
Worksheets(lCount).Name = "Day " & 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 *

 

[Floyd Bates]

Mr. Bovey,

Thanks so much for your assistance.

Floyd Bates