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:
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.