Introduction
When automating tasks with Excel VBA, there are often situations where you want to implement a flow like “Copy a cell -> Paste it into another file -> Close the original file.”
I was trying to build exactly this process, but an error occurred when trying to “Close the source file.” Upon investigation, I found that the cause was the “Cut (Copy) Mode” remaining active after the copy operation.
In this article, I will summarize how to cancel this Cut/Copy mode.
Cause of Error: Copy State Not Cancelled
The main reason an error occurs when trying to close a file after copying in VBA is that the CutCopyMode has not been cancelled.
Excel cannot close a file while it is still in a copy state (the “marching ants” border is active), so you must explicitly cancel this state.
Solution: Use Application.CutCopyMode = True
To cancel the copy state, simply add the following line:
Application.CutCopyMode = True
Alternatively, if you want to completely clear the clipboard, you can use False (there is no difference in terms of preventing the error).
Example of Actual Processing
Below is a sequence of processes that “Copies the value of cell A1 in book1.xlsx, pastes it into cell A1 of the workbook containing the VBA code, and then closes book1.xlsx.”
Workbooks("book1.xlsx").Activate
Range("A1").Copy
ThisWorkbook.Activate
Worksheets("Sheet1").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = True
Workbooks("book1.xlsx").Activate
Workbooks("book1.xlsx").Close
Explanation of the Code
- Line 1: Activates the source file (
book1.xlsx). - Line 2: Copies cell A1.
- Lines 3-4: Activates the workbook containing the VBA and pastes the value into cell A1 of Sheet1 (Values only).
- Line 5: Cancels the copy state (This is the key point).
- Lines 6-7: Reactivates the original file and executes the close process.
Important Points
- Using
Application.CutCopyMode = Falseworks the same way, but explicitly setting it toTrueis also possible to cancel the mode. - When using
PasteSpecial, you must maintain the copy state until the moment you paste. - If you try to close the file without cancelling
CutCopyMode, you may encounter errors like “Cannot complete operation” or “Cannot close file.”
Summary
When closing a file after copying in VBA, it is mandatory to cancel the copy state using Application.CutCopyMode.
Adding just one line can prevent errors and ensure smooth automated processing. I hope this helps anyone trying to build a similar process.
