I have a project with a lot of code that opens other workbooks and copies
large amounts of data from workbook to workbook.
Some aspect of the code must tie up a large amount of system resources and
keep it tied up after the code has finished executing because it often
returns a "not enough resources to complete the operation" error from
excel.
The user steps through a number of processes by clicking command buttons
(on a worksheet) sequentially. In brief, these a
- Enter a month period in a cell (like "2007-03"
- Run a sub that copies a file from a remote server to a local server and
give the file a name based on the text they entered in step 1.
- Run a sub that opens the downloaded file and copies a heap of data into
the main workbook (using copy, pastespecial, cutcopymode = false)
- Run a sub that copies 52 worksheets into a new workbook then copies every
cell on every sheet (cells.copy) in the new book and paste it all as values
(to remove the worksheet formulas). /This is the bit that causes the
resources issue/.

Is this just too much to ask a little dell workstation to do?
Do I need to release the memory used to copy the whole worksheets into a
new book?
Is there a better way to convert the worksheet formulas in 52 worksheets
into values?

I can post parts of the code if it will help.

TIA
--
Damien