Use of Application.CutCopyMode = False in VBA Excel – debugvba.com

Application.cutcopymode vba excel

Objective

To understand the usage of statement “Application.CutCopyMode = False”.

Usage of “Application.CutCopyMode = False” statement

When we copy the data in any cells, first the data is copied into clipboard, then from clipboard, it is pasted into target cell. If we use “Application.CutCopyMode = False”, then it clears the memory allocated to clipboard which contains the copied content. Which means after using “Application.CutCopyMode = False” we cannot paste anything because clipboard is empty. An error will be shown, which we shall discuss late in this post.

Case 1

In the code below we have copied the range A1:B5 once, then we have pasted it 2 times, once at F1 and J1 respectively. After that we have used “Application.CutCopyMode = False” statement.This code does not throw an error.

 

Sub CutCopyMode_1()

Range("A1:B5").Select
Range("A1:B5").Copy

Range("F1").Select
ActiveSheet.Paste

Range("J1").Select
ActiveSheet.Paste

Application.CutCopyMode = False

End Sub

 

In the image below, on right side we can see that after executing the VBA code, data from cell A1:B5 is pasted 2 times on cell F1 and J1.

 

Application.cutcopymode vba excel

 

Case 2

Now in the below code we have changed the position of “Application.CutCopyMode = False” statement. Here first we have copied the range A1:B5, then we have pasted it on cell F1. After this we have used “Application.CutCopyMode = False”. Once we have used this statement, we can not paste anything because the memory allocated to clipboard, which contains the data is released and now clipboard is empty. Now when we try to paste the data in cell J1, it will throw an error. We can see the error in the image below.

 

Sub CutCopyMode_2()

Range("A1:B5").Select
Range("A1:B5").Copy

Range("F1").Select
ActiveSheet.Paste

Application.CutCopyMode = False
Range("J1").Select

ActiveSheet.Paste

End Sub

Application.cutcopymode vba excel

Below shown error is shown when we try to paste the data 2nd time at cell J1. Since we had already used  “Application.CutCopyMode = False” statement before pasting the data at cell J1, it made the clipboard empty , so we can not paste anything after using this statement.

 

Application.cutcopymode vba excel

 

Apart from the above code, if we use “Application.CutCopyMode = False” at one point in code , then later in the code we can again copy and paste the data, that will not be impacted due to earlier usage of “Application.CutCopyMode = False”

You can get more detail by the link provided in the reference section.

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.application.cutcopymode

Post you may like 

Use intersect method in VBA Excel -debugvba.com