Updated 01/08/2017 to take into account Excel 2016 updates.
If you have ever used copy and paste whilst recording a Macro you will have seen the code which refers to the CutCopyMode:
Application.CutCopyMode = False
You may wonder why is it there? If you use the copy and paste code without that statement it still appears to work fine. So does it serve any purpose?
What is CutCopyMode = False for?
When we copy in Excel it uses the Excel Clipboard as a temporary home for the content we have copied. This is when we see the line of marching ants around the copied range.
The content can then be pasted into another part of Excel.
The “Application.CutCopyMode = False” code carries out two functions:
- Removes the marching ants to prevent the user from pasting the cells previously copied.
- Prevents the ‘There is a large amount of information on the Clipboard….’ warning message,
The Macro Recorder is simply finalizing the copy and paste process by preventing any further pasting of the content. This works in a similar way to pressing the Esc key to remove the marching ants.
What about CutCopyMode = True?
You might think that CutCopyMode = True would turn on the marching ants, but it doesn’t. The opposite of Application.CutCopyMode = False is not Application.CutCopyMode = True.
When we manually cut or copy cells the status of the CutCopyMode is either xlCopy or xlCut. When we paste the content, the status of the CutCopyMode is used by the paste method, so it knows whether to perform a cut or a copy.
Should you use CutCopyMode = False?
If we are writing VBA code it may not be necessary to use the CutCopyMode at all. Not because it is bad code, but because there are often other ways to copy and paste content without using the clipboard at all.
We can paste values, by using the Value property of the range object:
Sub CopyAndPasteValuesOnly() ActiveSheet.Range("A1:A20").Value = ActiveSheet.Range("B1:B20").Value End Sub
If we wish to copy and paste more than just the values, by using the copy and send to destination method:
Sub CopyAndPasteValuesOnly() Activesheet.Range("A1:A20").Copy ActiveSheet.Range("B1:B20") End Sub
It is not necessary to define the full range of the destination, we could just use the reference of the first cell instead.
Ultimately, the decision of whether to use CutCopyMode = False comes down to this: if at the end of executing the code the marching ants are visible, and we do not want them to be, then we should use CutCopyMode = False, otherwise don’t worry about it.
Excel 2016 update to Application.CutCopyMode = False
In June 2017 Microsoft issued an update for Excel 2016. Prior to this the first action after a copy/cut had to be paste. If other actions where undertaken, the copy/cut would be canceled automatically and the marching ants would disappear.
Following the update in June 2017, it is no longer necessary to paste directly after the copy/cut. Therefore, the copy/cut is more likely to remain active (with the marching ants displayed) after recording a macro. As a result, Application.CutCopyMode = False will be required more often.
The basic principle still remains true: if at the end of executing the code the marching ants are visible, and we do not want them to be, then we should use CutCopyMode = False, otherwise don’t worry about it.