COL_H
08-27-2005, 06:26 AM
http://vbaexpress.com/forum/images/smilies/banghead.gif I am working with Excel 2003 on Windsows XP. I am fairly good in coding VBA and Excel in general, but I am stumped on this one. I am having an issue with large spreadsheets (containing multiple spreadsheets each with many VLOOKUP statements).
Specifically - I have a Workbook with approximately 40 - 50 spreadsheets in it - each conaining 40 - 50 VLOOKUP statements. The Workbook is then in the 8 - 10 MB range before it is converted to a new Workbook.
Each time I try to convert this Workbook to a new one (a duplicate Workbook with only minor date changes) - I keep getting the following message after about 6 - 8 spreadsheets are created:
*********************************************************
Run time error '1004'
Copy method of Worksheet class failed
*********************************************************
If I close the Workbook and shut down Excel and restart Excel, it can sometimes create a few more spreadsheet - but never all of them in one pass.
In researching the problem, I believe that there is code in VBA that will allow me to clear (e.g. Reset or Clear) the internal memory in Excel, BUT I cannot find any examples of how to enter the code.
I have tried clearing the Clipboard with each new spreadsheet created, but this has not helped.
*********************************************************
Application.CutCopyMode = False
*********************************************************
Are there any examples of code that I can use in my VBA module that will allow me to clear the Excel memory while Excel is active?
Thanks for the help.
file:///C:/DOCUME%7E1/MARK/LOCALS%7E1/TEMP/moz-screenshot-2.jpg
Specifically - I have a Workbook with approximately 40 - 50 spreadsheets in it - each conaining 40 - 50 VLOOKUP statements. The Workbook is then in the 8 - 10 MB range before it is converted to a new Workbook.
Each time I try to convert this Workbook to a new one (a duplicate Workbook with only minor date changes) - I keep getting the following message after about 6 - 8 spreadsheets are created:
*********************************************************
Run time error '1004'
Copy method of Worksheet class failed
*********************************************************
If I close the Workbook and shut down Excel and restart Excel, it can sometimes create a few more spreadsheet - but never all of them in one pass.
In researching the problem, I believe that there is code in VBA that will allow me to clear (e.g. Reset or Clear) the internal memory in Excel, BUT I cannot find any examples of how to enter the code.
I have tried clearing the Clipboard with each new spreadsheet created, but this has not helped.
*********************************************************
Application.CutCopyMode = False
*********************************************************
Are there any examples of code that I can use in my VBA module that will allow me to clear the Excel memory while Excel is active?
Thanks for the help.
file:///C:/DOCUME%7E1/MARK/LOCALS%7E1/TEMP/moz-screenshot-2.jpg