PC becomes unresponsive when extracting data from MANY workbooks

[bongaan]

I have a macro which extract data from many workbooks into one workbook. My macro works fine, but after processing quite a few workbooks it suddenly slows down considerably. I don't understand why, I have a CPU and RAM widget on my desktop and it shows that my CPU usage always stay below 50% and my RAM usage between 60-70% where it's normally. So I don't know why it slows down. Below my code, any help will be much appreciated.

Please Login or Register  to view this content.

[The_Prist]

Please Login or Register  to view this content.

- wrong.
You must use:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

And you macro need to optimize. If you will be use a arrays - macro will be faster. After a while I will try to lay out the sample code

[The_Prist]

something like what:

Please Login or Register  to view this content.

[bongaan]

In the mean time I have found that Task Manager shows that the memory used by EXCEL.EXE increase over time and it is when windows start to use the HDD when the macro slows down considerably. I then tried a different method, instead of opening each file, reading from it and then closing it again, I now use a query to read each file into the workbook. This solved the problem! The memory used by EXCEL.EXE did not increase. Anybody understands why opening and closing cause this? It seems as if excel is not releasing the memory when closing the file, why so?

[akhileshgs]

I use the following code to remove the copied data from the clipboard..
May be u can use the same

Application.CutCopyMode = False 

[bongaan]

I use the following code to remove the copied data from the clipboard..

My code do not make use of copy/cut, so I don't believe it's anything to do with the clipboard memory