Application.CutCopyMode = False (How to use it)

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.

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:

1. Paste the copied content from the clipboard, or
2. Perform any other action to leave the cut/copy mode.
3. 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.

Setting Values

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.

ActiveSheet.Range("A1:A20").Copy ActiveSheet.Range("B1")

Conclusion

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.

Related posts:

• Cell and Range VBA properties and methods
• Useful VBA codes for Excel (30 example macros + Free ebook)
• VBA Code Library