When using copy and paste in a macro it is a good idea to clear the clipboard at the end of the macro. If you don’t, the user could use paste to paste the last thing you had copied in the macro.
Luckily it is easy to clear the clipboard. It’s a one line command
Application.CutCopyMode=False
Usually you place this command near the end of your macro. You can also include it after each paste in your code to make sure the clipboard is cleared between each copy and paste.
If you use error handling code you would place the command with all the code to run when the macro finishes.
Note: (Added 5 November 2018) The command above clears Excel’s clipboard, but it won’t clear the clipboard inside the VBA window itself eg if you copy text inside the VBA window it is unaffected by the above code. Note it is unusual to create VBA code to affect the VBA window – I have done it once in about 20 years of VBA coding.
That was really the solution I was looking for! Every now and then a message requesting me to manage the clipboard pops up while running my macro in excel, and just by adding this command after the “paste” command lines, I can stop worrying about whether the clipboard is full or not!
Thanks a lot!
Great! Glad it helped.
Thanks, it is a one-line code and very simple, but very helpful.
Glad it helped – its been a popular post.
This solution doesn’t work in case of pasting we coppied from other sources (for instance notepad).
Hi Mike
That’s right it only clears the Excel clipboard.
To clear the Windows clipboard seems more complex – try this
http://www.vbaexpress.com/kb/getarticle.php?kb_id=205
Thank’s
That’s exactly what I needed.
I’m copying pictures when opening a worksheet and cutcopymode=false doesn’t work in that situation.
br,
cesar.
Glad it helped. It has been a popular post.
Great. Thank you very much.
I built on this command, and then did this in my Excel VBA code. When I am “finished” I select any cell in the page in my document, copy it, and set CutCopyMode to False. E.g.:
Range(“H1”).Select
Selection.Copy
Application.CutCopyMode = False
That should clear out the clipboard entirely.
Hi Mark
You can just use
Application.CutCopyMode = False
anywhere in your code to clear the clipboard – doesn’t have to be just after a cop. – sometimes you include it in the exit or error handler to make sure the clipboard is clear when the code finishes.
Regards
Neale
For some reason whenever i inserted a row using Range.EntireRow.Insert, it was inserting the previously copied cells into every single cell across the entire row. Very frustrating, but this fixed it. Thank you!
Mike
Hi Mike
Yes Insert acts like paste if you have copied before using it.
Regards
Neale