31

Well first of all I found couple of answers while googling but most of the forums are blocked in my Office Network hence asking this question here! One more intention is to get an answer in plain English :P

I understand if we set Application.CutCopyMode = False then the Copied/Cut results will be vanished (i.e. memory will be cleared) but when should we use this and when not to use this? Can anyone please help?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Saikat
  • 14,222
  • 20
  • 104
  • 125
  • 1
    More information can be found at [Should I turn .CutCopyMode back on before exiting my sub procedure?](http://stackoverflow.com/questions/33833318/should-i-turn-cutcopymode-back-on-before-exiting-my-sub-procedure). –  Nov 26 '15 at 00:06

3 Answers3

37

By referring this(http://www.excelforum.com/excel-programming-vba-macros/867665-application-cutcopymode-false.html) link the answer is as below:

Application.CutCopyMode=False is seen in macro recorder-generated code when you do a copy/cut cells and paste . The macro recorder does the copy/cut and paste in separate statements and uses the clipboard as an intermediate buffer. I think Application.CutCopyMode = False clears the clipboard. Without that line you will get the warning 'There is a large amount of information on the Clipboard....' when you close the workbook with a large amount of data on the clipboard.

With optimised VBA code you can usually do the copy/cut and paste operations in one statement, so the clipboard isn't used and Application.CutCopyMode = False isn't needed and you won't get the warning.

  • Interesting and thanks! I will wait for few more comments to come before I mark your answer as accepted :) – Saikat Jul 12 '13 at 07:46
  • I tried this out in Excel 2010 and it doesn't appear to do anything anymore. I got rid of my Application.CutCopyMode = False – chrips Aug 15 '14 at 17:19
  • 3
    is there any scenerio that uses application.CutCopyMode = True? – DeerSpotter Aug 28 '15 at 16:03
  • Can someone pls. show optimised vba code to copy/paste the anonymous user here is writing about. – Timo Sep 29 '21 at 11:46
  • It may not be obvious, but I think what is going on as well is that you can copy multiple things to the clipboard. These all can be accessed via GUI if possibly not easily through code (idk). So it's not the case that the last code copy statement simply replaces the clipboard. It adds to it. Being the last copy, it is what usual code paste statement would utilize as if the rest of the clipboard contents no longer existed. To actually free that older memory, however, do Application.CutCopyMode = False at some point before the desired copy statement or after the associated paste(s). – Jose_X Jun 29 '23 at 13:43
16

Normally, When you copy a cell you will find the below statement written down in the status bar (in the bottom of your sheet)

"Select destination and Press Enter or Choose Paste"

Then you press whether Enter or choose paste to paste the value of the cell.

If you didn't press Esc afterwards you will be able to paste the value of the cell several times

Application.CutCopyMode = False does the same like the Esc button, if you removed it from your code you will find that you are able to paste the cell value several times again.

And if you closed the Excel without pressing Esc you will get the warning 'There is a large amount of information on the Clipboard....'

Osama
  • 161
  • 1
  • 3
1

There is a good explanation at https://stackoverflow.com/a/33833319/903783

The values expected seem to be xlCopy and xlCut according to xlCutCopyMode enumeration (https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/xlcutcopymode-enumeration-excel), but the 0 value (this is what False equals to in VBA) seems to be useful to clear Excel data put on the Clipboard.

George Birbilis
  • 2,782
  • 2
  • 33
  • 35