Sleeper: Memory or Cache Problem

[COL_H]

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

[mdmackillop]

Hi ColH
Welcome to VBAX.
This is a bit out of my expertise. I tried the following code to simulate your problem


Sub DoCopy()
Application.DisplayAlerts = False
For i = 1 To 50
Sheets("Sheet1").Copy Before:=Workbooks("testnew.xls").Sheets(1)
Debug.Print Application.MemoryFree & " bytes free"
Next
Application.DisplayAlerts = True
End Sub


The macro is halting during execution after 26 copies. The memory free figure figure remained constant as 1048576 bytes free, and the code will not run again until excel is closed and reopened.
HTH
Regards
MD

[TonyJollans]

Application.MemoryFree, unfortunately, doesn't work! It always returns 1MB.

[TonyJollans]

Hi Col_H,

Welcome to VBAX!

I don't know a huge amount about it, but Excel has its own memory manager and built in limits. Sooner or later, everybody runs into one or other of the limits and there really isn't much you can do about it.

That said, your error message doesn't actually specify memory problems, and the limits have been vastly increased in 2003 so (assuming your physical memeory and/or swap file are big enough) you may have another problem.

Can you successfully open two copies of your workbook at the same time in one instance of Excel?

If you're trying to duplicate the whole workbook, why not just use Windows Copy and Paste?

[MWE]

Hi Col_H,

...

If you're trying to duplicate the whole workbook, why not just use Windows Copy and Paste?
that was my first thought.

I have had problems in the past trying to be elegant about copying, i.e., only parts and pieces and ran into problems. I ended up just copying the whole thing and then editing out what I did not want ...