MrExcel.com - Your One Stop for Excel Tips & Solutions

Inserting many sheets via macro causes premature memory problem

Posted by Bill on January 17, 2001 9:12 PM

I have a reporting application. Data comes in from our mainframe. For each of the 48 unique cost centers, the code does the following:
1) Makes a copy of the Template sheet
2) Fills in certain values on the new sheet
3) Inserts detail rows as necessary
4) Uses activesheet.move to move the new sheet to a 2nd book.

During execution of this macro, I am attempting to do ActiveSheet.copy and ActiveSheet.Move 48 times each. I have tried the program on three machines, and it always errors out on the ActiveSheet.move. I always get a 1004 Move method of Worksheet Class failed. On a machine with lots of memory, I get through 25 cost centers. On the other machines, I may get through 15 cost centers. If I attempt to re-run, I may only get through 5 cost centers, but it always blows up on the ActiveSheet.Move line.

The actual line of code is:
ActiveSheet.Move After:=Workbooks(RptFile).Sheets(SheetCount)
RptFile and SheetCount are defined variables to hold the name of the new book and the number of sheets in that book.

Excel says that the number of sheets in a book is limited by the memory of a workbook. The template sheet is 42 rows by 6 columns. Outside of a macro, I can easily create a workbook with 64 of these sheets without any memory problems.

I have tried various tricks to conserve memory. I tried a counter so that after every 10 reports, the report file gets saved, closed, and a new report file is started. My hunch is that as long as my macro is executing, the memory is not actually freed up.

I even tried a modified routine that: (1) Copies the template to a new sheet in the activebook, (2) Performs the calculations, (3) Saves some key totals, then (4) Delete the new sheet instead of moving to the new book. This time, instead of 15 sheets, I was able to process 45 sheets before the ActiveSheet.copy failed.

One last possibility is that the template sheet has about 10 named ranges. When I manually tried to copy the sheets, I do receive the error saying that a sheet I want to move contains the name "xxxx" which is already defined in the destination workbook. Could this be causing the 1004 error? If so, I would think it would happen on the 2nd move, not the 15th or 25th.

Any ideas would be greatly appreciated.

Bill

Posted by Dave Hawley on January 17, 2001 11:01 PM


Hi Bill

Why do you think this is a memory problem ? If you have some code that is setting a Variable to an object over and over place the line:

Set MyVariable = Nothing'Where "MyVariable" is the name of your Varaible

In your code where applicable, this will release memory.


You maybe able to use the code below to move your sheets. It will move all but one sheet from the active workbook.


Dim sht As Worksheet
Dim ActiveFile As String
ActiveFile = ThisWorkbook.Name & ".xls"
Application.EnableEvents = False
Application.ScreenUpdating = False
On Error Resume Next
For Each sht In ThisWorkbook.Sheets
sht.Move After:=Workbooks(RptFile).Sheets(SheetCount)

Windows(ActiveFile).Activate
Next
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


Hope this helps
DaveOzGrid Business Applications