In Macro - Clear Clipboard / Free up Memory

[Mr Mike]

Hello,

I have a macro that does a ton of large copy/pastes of huge formulas. In order to reduce the size of the "report" file, my macro then turns all the data into values.

I'm encountering an error message when my macro saves the file. It says I don't have enough system resources to complete the selected task (save the file).

I'm under the assumption this mean memory (since I have gobs of HD space). I tried increasing my virtual memory to max, but that doesn't help. I'm just wondering if my "copies" are still stored in memory somewhere, and that is why my machine is telling me I don't have enough resources. If so, is there any code I can apply that will remove the "copy" from memory once I paste it in?

Any thoughts would be helpful. Thanks!

[Ken Puls]

Hi there,

KB Entry to clear the clipboard can be found here

Alternately, you could set the application.cutcopymode=false, and I think that may take care of it for you.

HTH,

[Mr Mike]

Thanks for your assistance Ken. I appreciate your time. Unfortunately it didn't seem to do the trick for me.

I added your code to a mdule of its own, and set your macro to run after big pastes, and before saves in my code. I've tried both alternatives seperately, and in combination, but unfortunately I'm still receiving that error message.

Strangely enough, I turned on my task manager and clicked on "Performance" to view the free memorey and CPU usage, and then ran my macro. It seems to be perfectly fine, and doesn't report back high memory usage. I've tried running it on other computers and have still the same problem. Resource availability appears fine, yet I still get the message.

Grrrrr....

[Sir Babydum GBE]

Can you not get your macro copy the info from a blank cell (or a few blank cells - one at a time) to replace the big stuff in the clipboard with nothing - then do the save?

[Ken Puls]

Are you creating/referencing any objects that should be released in your code? Say creating instances of other applications or something?

The only other thing I can think of is possibly that your workbook may be corrupted somehow. Is it a big job to copy everything to a new book and test it there?

I have run into an issue before where everything appeared fine, but errors were occuring and copying to a new book solved them.

[Zack Barresse]

Can you post your code? The KB entry works fine for me. It would be nice to see how you are actually using the code together. It's a Windows API call, so it clears the Windows clipboard (system wide).

Did you try both of Ken's suggestions?

[Mr Mike]

Thanks for all your help guys. I found the culpirt. For some strange reason this particular formula was causing my problems.

=VLOOKUP(CONCATENATE(A6,$L$5),'[Division Raw Data.xls]Adjusted OH by Div'!$E:$F,2,FALSE)

I have a hidden row of formulas, and my macro uses this row to copy/paste to all rows below in my document, and then it copies/pastes the values of the cells (to reduce file space when saving). This one formula in my hidden row, when deleted, would allow the file to save normally when the macro runs. I then re-added this formula, and then I started getting my same problems. >=o( So.... I'm having my macro write the formula into the hidden row for me, , do its copy/pastes, and then delete it from the hidden row before saving the file.

I have no idea why this one formula would be causing all these problems. It's the only one I have with a concatenate, but still, I have other similar formulas in other documents... Sigh....

Thank you all so much for all of your help!