If you use copy and paste while recording a Macro, we will see the code which refers to Application.CutCopyMode = False. You may wonder why it’s there? What does it do?
In this post, we answer all your question about Application.CutCopyMode.
Table of Contents
Application.CutCopyMode = False in recorded macros
If we record a VBA Macro where we copy and paste the recorded code might look similar to the following:
Range("A1:A10").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Application.CutCopyMode = False
If we run the VBA code to copy and paste without that line of code, it still works fine. So, does it serve any purpose?
What is Application.CutCopyMode = False for?
In Excel, when we copy or cut a range of cells, the content is temporarily stored in Excel’s clipboard. This is when the animated dotted line appears and moves around the outside of the range; commonly referred to as the marching ants.
Look at the screenshot below; the marching ants show that we are in cut or copy mode, with cells A1 to C4 stored in the clipboard. The message in the status bar also changes to indicate which mode we are in.
Having copied a cell range, we can now:
- Paste the copied content from the clipboard, or
- Perform any other action to leave the cut/copy mode.
- Press Esc key to leave CutCopyMode without needing to perform an action on the worksheet.
Application.CutCopyMode = False is the VBA action of leaving cut/copy mode.
- The marching ants removed
- Status bar returns to its default status
- Memory used for the copied content in the clipboard is cleared
After using Application.CutCopyMode = False, it is no longer possible to paste from the clipboard using VBA. Trying to do so results in an error.
Run-time error’1004′: Paste method of Worksheet class failed
If we are in cut/copy mode and more than 101 cells have been selected, closing the workbook results in the following warning message.
There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another program later?
Therefore, resetting the CutCopyMode before closing a workbook prevents this warning message from appearing.
Will Application.CutCopyMode clear the clipboard?
In the previous section, it was noted that Application.CutCopyMode = False clears the clipboard. However, this relates to Excel’s clipboard and not the Windows clipboard.
The Windows clipboard still retains the items it had previously and is unaffected by this setting.
What about CutCopyMode = True?
Logically, we would think Application.CutCopyMode = True would turn on the marching ants, but it doesn’t. Microsoft’s own documentation indicates it should, but I’ve never made this work, or seen others use it.
The opposite of Application.CutCopyMode = False is not Application.CutCopyMode = True. When we cut or copy cells, the status of CutCopyMode is either xlCopy or xlCut. Each status also has a numerical value:
- False = 0
- xlCopy = 1
- xlCut = 2
When pasting cells, Excel reads the CutCopyMode status to know whether it should cut or copy.
The following VBA will determine which mode Excel is in:
Select Case Application.CutCopyMode Case False MsgBox "CutCopyMode = False" Case xlCopy MsgBox "CutCopyMode = xlCopy" Case xlCut MsgBox "CutCopyMode = xlcut" End Select
Should you use CutCopyMode = False?
If we are writing VBA code from scratch, it is rarely necessary to use CutCopyMode at all. Not because it is bad code, but because there are better ways to copy and paste which don’t use Excel’s clipboard, making it faster to execute.
We can paste values using the Value property of the range object. The following code places the values from cells B1 to B20 into cells A1 to A20 without using Excel’s clipboard.
Sub CopyAndPasteValuesOnly() ActiveSheet.Range("A1:A20").Value = ActiveSheet.Range("B1:B20").Value End Sub
Copy with Destination
If we wish to copy and paste more than just values, we can use the destination argument of the copy method. This also avoids the need to copy and paste.
Sub CopyAndPasteValuesOnly() ActiveSheet.Range("A1:A20").Copy Destination:=ActiveSheet.Range("B1:B20") End Sub
It is not necessary to define the full range of the destination; we can just reference the first cell. Excel knows how many cells we have copied and will adjust the paste range accordingly.
Ultimately, the decision of whether to use Application.CutCopyMode = False in our VBA code comes down to this: if at the end of executing the macro, the marching ants are visible, and you don’t want them to be, then use it, otherwise don’t worry about it.
- Cell and Range VBA properties and methods
- Useful VBA codes for Excel (30 example macros + Free ebook)
- VBA Code Library
Discover how you can automate your work with our Excel courses and tools.
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.