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.
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.
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.
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
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.
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.
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.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- 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.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: