Application.Calculation = xlCalculationManual statement causing error 1004 at runtime in VBA Copy-Paste procedure
I have VBA code that copies the first line and pastes values across multiple lines. The following code works fine and inserts the lines as expected:
Sub Macro1()
Dim i As Long
Application.Calculation = xlCalculationManual
Range("A1:M1").Select
Selection.Copy
For i = 1 To 50
Range("A" & i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next i
End Sub
However, if I move Application.Calculation = xlCalculationManual
down two lines as shown below, then the code throws a 1004 runtime error:
Sub Macro1()
Dim i As Long
Range("A1:M1").Select
Selection.Copy
Application.Calculation = xlCalculationManual
For i = 1 To 50
Range("A" & i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next i
End Sub
I searched the VBA language reference site here: http://msdn.microsoft.com/en-us/library/office/jj692818(v=office.15).aspx and the Excel developer reference site here: http: // msdn .microsoft.com / en-us / library / office / ff194068 (v = office.15) .aspx .
Also, I checked this error using Excel 2010 running on Windows 7 and 2013 running on Windows 8.1.
Can anyone help me understand why location Application.Calculation = xlManualCalculation
will affect how the code works?
EDIT:
I ran some additional tests to see if the focus was lost or the clipboard was reset. First, to see if the focus was lost, I recorded a macro that copied the first line with ctrl + x, then I changed the workbook calculation mode, and then press Ctrl + X again without reselecting the cells. This is the result of the macro:
Sub MacroFocusTest()
Range("A1:M1").Select
Selection.Copy
Application.CutCopyMode = False 'Macro recording entered this.
Application.Calculation = xlManual
Selection.Cut 'Range("A1:M1") is cut on the worksheet suggesting focus was not lost.
End Sub
Then I injected a variable into my original Macro1 to capture Application.CutCopyMode at various stages of execution. The next output is a macro:
Sub Macro1()
Dim i As Long
Dim bCCMode as Boolean
bCCMode = Application.CutCopyMode ' False
Range("A1:M1").Select
Selection.Copy
bCCMode = Application.CutCopyMode ' True
Application.EnableEvents = False ' Included because I mention in comments no error is thrown using this
bCCMode = Application.CutCopyMode ' True
Application.Calculation = xlCalculationManual
bCCMode = Application.CutCopyMode ' False
For i = 1 To 50
Range("A" & i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next i
End Sub
Based on the results of these two tests, I believe that Application.Calculation = xlCalculationManual does not cause the range to lose focus, but clears the clipboard.
source to share
As per your specific question, the answer is: the Application.Calculation = xlCalculationManual
statement erases the clipboard memory, resulting in a subsequent runtime error in the code snippet.
Note: There is another suggested explanation: "The copy of Excel will lose focus"; it might just be a semantic difference indicating the same effect and just phrased differently, but for better clarity I prefer this one i.e. buffer memory (or what you call dark register) lose value or reference.
The test parameters to validate / illustrate the concept and detailed explanation are as follows:
'Error occured because a statement
'Application.Calculation = xlCalculationManual
'or Application.Calculation = xlAutomatic
'or Application.Calculation = xlManual
'placed after `Selection.Copy` clears the clipboard memory;
'thus there is nothing to paste and Error 1004 occured
'as demonstrated in the added test code block
Sub YourMacroWithProblem()
Dim i As Long
Range("A1:M1").Select
'Selected Range content is placed to Clipboard memory
Selection.Copy
'This statement erases Clipboard memory
Application.Calculation = xlCalculationManual
' test if clipboard is empty ---------------------
On Error Resume Next
ActiveSheet.Paste
If Err Then MsgBox "Clipboard is Empty": Err.Clear
'-------------------------------------------------
For i = 1 To 50
Range("A" & i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next i
End Sub
Also, there is an old discussion on a similar topic: Stop VB from Clearing Clipboard (link: http://www.mrexcel.com/forum/excel-questions/459793-stop-vbulletin-clearing-clipboard-3.html ).
You might consider the following solution to your problem, optimized for speed and reliability:
Sub Macro2()
Dim i As Long
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 1 To 50
Range("A1:M1").Copy Destination:=Range("A" & i)
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Note. Unlike your problematic piece of code, there is no need for operator Select
and clipboard copy / paste operations in the proposed solution, so any potential side effects will be minimized as well.
Hope this helps. Respectfully,
source to share
You change focus between copy and paste. When you do this, Excel loses the copied data, giving you an error when you try to paste. The same will happen if you try to do it from a worksheet in this order.
Excel doesn't really use the system clipboard like other programs. I believe this is due to issues related to changing cell references in the copied data.
You can try using the Office Clipboard if you only want to paste values, but there is no VBA support that I am aware of in recent versions of Excel.
You may find this answer of interest. It refers to Excel developer comment Prevents Excel from deleting copied data for pasting after certain operations without Office Clipboard
source to share