Application.CutCopyMode = False

Application.CutCopyMode = False

If you use copy and paste while recording a Macro, you will see the code which refers to Application.CutCopyMode = False.  You may wonder why it’s there?

The recorded code looks a bit like this:

Range("A1:A10").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

If you use VBA to copy and paste without that line of code, it still works fine.  So, does it serve any purpose?  And is there a better option?

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.

Marching ants in CutCopyMode

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. Pressing the Esc key will remove 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 failed - VBA error message

If we are in cut/copy mode and more than 101 cells have been selected, closing the workbook will result in the following warning message.

Large amount of information on clipboard

Therefore, resetting the CutCopyMode before closing a workbook will prevent this warning message from appearing.

What about CutCopyMode = True?

Logically, you might 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

Generate accurate VBA code in seconds with AutoMacro

AutoMacroExample

AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.

Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.

Should you use CutCopyMode = False?

If we are writing VBA code from scratch, it may not be 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.

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,

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 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.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

6 thoughts on “Application.CutCopyMode = False

  1. Daniel says:

    in regards to Application.CutCopyMode = False

    is there a way to have this work from an inactive sheet?

    I’m running a copy and paste macro from sheet 1 to sheet 2 and 3. Once the macro is complete the last section that is copied is highlighted on sheet 2 and 3 how can I avoid this? App false only works when the sheet is active. I’ve tried using a range select but again only on active sheets.

    • Excel Off The Grid says:

      The cell selection is a separate activity from copy and paste. You can specify exactly which cell is selected on each sheet, but you do need to activate it first.

      Application.ScreenUpdating = False
      
      Sheets("Sheet2").Activate
      Range("A5").Select
      
      Sheets("Sheet3").Activate
      Range("E12").Select
      
      Application.ScreenUpdating = True
      
  2. Jean-Francois Lemieux says:

    Useful. Now I know. This brings another : Is there a way to work with the clipboard in VBA?

Leave a Reply

Your email address will not be published. Required fields are marked *