Excel Crashes When Copying Multiple Sheets in VBA

[Tim Hubbard]

Is there a way to prevent Excel from leaking memory or
hitting its memory limit when using VBA code to autocopy
multiple sheets?? Some people seem to think that the
problem is caused because the sheet name being updated in
memroy becomes too long when making multiple copies
(Ex: "Sheet1111111111111111111"). I don't think this is
true since I encounter similar problems when trying to
delete multiple sheets using macro code.

I recenlty built a Forecasting Tool that uses a macro to
build several employee templates by making multiple copies
of a fixed template then renaming each copy and populating
it with data for each employee. I also made some
enhancements to a Project Management Tool that uses a
macro to build Project Manager Workbooks from a template
by making multiple copies.

The problem I am having is that there seems to be a memory
limit on the number of copies that can be made, and when
that memory limit is reached Excel either crashes or a
runtime error occurs and the macro stops at which point
Excel refuses to make any more copies. To get around this
problem so that the program runs to completion I have to
put in alot of extra code and error checking to catch when
this memory problem occurs and then close and save the
workbook while keeping track of where the progress and %
complete was when it had to be closed. I then have to
reopen the workbook and run the macro a second time so
that it can read in the progress data and finish where it
terminiated when the file was saved and closed.

Is there anything I can do in the code to periodically
free up memory or reset what ever is the cause of this
problem?? Finding a solution to this problem could also
potentially increase the process speed of my app as well.
Do anyone have any ideas??

 

[Rob Bovey]

Hi Tim,

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

This works especially well in situations like you describe, where the
copied sheets are structurally all the same. This means you can create one
template worksheet and use it as the basis for making as many copies as you
want. You just have to adjust the number of sheets copied at a time to your
situation. 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 *

 

[Tim Hubbard]

Great, that worked for now. I'm going to do some more testing, but I
really appreciate the help. Do you know why Excel has this problem?

Tim

 

[Rob Bovey]

Great, that worked for now. I'm going to do some more testing, but I
really appreciate the help. Do you know why Excel has this problem?

Hi Tim,

Glad it worked for you. No idea why this happens, other than it's just a
bug in Excel.

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