"Application.Calculation = xlCalculationManual" statement causing run-time error 1004 in VBA Copy-Paste procedure

Question

I have VBA code that copies the first row and pastes the values to multiple rows. The below code runs fine and pastes the rows 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 below, then the code throws a 1004 run-time 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've searched for information on 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.

Further, I've verified this error using both Excel 2010 running on Windows 7 and 2013 running on Windows 8.1.

Can someone help me understand why the location of Application.Calculation = xlManualCalculation would affect how the code runs?

EDIT:

I ran some additional tests to check if focus is lost or the clipboard is cleared. First to see if focus is lost I recorded a Macro that copied the first row with ctrl + x, then I changed the calculation mode of the workbook, then I hit ctrl + x again without re-selecting the cells. This is the resultant 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

Next, I entered a variable into my original Macro1 to capture the Application.CutCopyMode at various stages of execution. Follows is the resultant 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 does clear the clipboard.

score 2 · Answer 1 · edited Mar 20 '17 at 10:04

2

You are changing the focus between the copy and the paste. When you do that Excel loses the copied data, giving you the error when you try to Paste. The same thing would occur if you tried to do that from the worksheet, in that order.

Excel doesn't really use the system clipboard, like other programs. I believe this has to do with issues having to do with changing cell references in the copied data.

You could try using the Office Clipboard if you only want to paste the values, but there's no VBA support for that of which I am aware, in recent versions of Excel.

You might find this response of interest. It references a comment by an Excel developer Prevent Excel from clearing copied data for pasting, after certain operations, without Office clipboard

edited Mar 20 '17 at 10:04

Community

1
1

answered Dec 20 '14 at 02:39

Ron Rosenfeld

53,870
7
28
60

Thank you for your answer. Can you clarify what you mean by changing the focus? To what am I changing the focus? Do you know of any documentation that would help me understand the rules of focus? – astidham2003 Dec 20 '14 at 02:46
I do notice that if I place Application.EnableEvents = false in the code between the copy and paste, the code runs without error. – astidham2003 Dec 20 '14 at 02:58
@AStidham Search the web with something like *excel copy loses focus* and you'll find several discussions. – Ron Rosenfeld Dec 20 '14 at 03:07
Using Google I've searched for that term. The information I've found indicates that copying, then changing worksheets or entering something into another cell causes excel to lose focus. However, I've not found anything about assigning to properties. Also, if nothing can be done between copy and paste, then I must then wonder why does Application.EnableEvents = false not cause Excel to lose focus? – astidham2003 Dec 20 '14 at 03:17
@AStidham Some behaviors are not well documented. – Ron Rosenfeld Dec 20 '14 at 03:23
@AStidham In may answer, I have added a link to another comment on this behavior, from the SuperUser forum. Perhaps it will aid your understanding, although not definitive. – Ron Rosenfeld Dec 20 '14 at 11:16

Alexander Bell · Accepted Answer · 2014-12-20T07:42:18.127

Pertinent to you particular question, the answer is: Application.Calculation = xlCalculationManual statement erases the Clipboard Memory, which causes the subsequent Run-time error in your code snippet.

Note: there is another suggested explanation as 'Excel copy loosing the focus'; it might be just a semantic difference, pointing to the same effect and just worded differently, but for better clarity I prefer this one, i.e. clipboard memory (or whatever you call that temp register) loosing value, or the reference.

The test settings to prove/illustrate the concept and detailed explanation 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 similar topic: Stop VB from Clearing Clipboard (link: http://www.mrexcel.com/forum/excel-questions/459793-stop-vbulletin-clearing-clipboard-3.html).

You may 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 code snippet, there is no need for the Select statement and Clipboard Copy/Paste operations in suggested solution, thus any potential side effects will be minimized, either.

Hope this may help. Kind regards,

Thank you for your answer. This does not help in my particular case because it does not pertain to my question. — astidham2003, Dec 20 '14 at 04:08
I have extended my answer with explanation pertinent to the cause of your error. I would recommend you to refer to the optimized solution that I have provided. Best regards, — Alexander Bell, Dec 20 '14 at 04:32
Please provide where you have found that Application.Calculation = xlCalculationManual erases the clipboard. The documentation at the excel developer reference ( http://msdn.microsoft.com/en-us/library/office/ff835845(v=office.15).aspx ) makes no mention of this. Further, according to information I have found when searching for 'excel copy loses focus' as suggested by Ron Rosenfeld, excel does not use the clipboard when copying. — astidham2003, Dec 20 '14 at 04:59
@AStidham: your question is answered clearly - because Clipboard Memory is Empty. Now, you are asking ANOTHER question - 'WHY CLIPBOARD IS EMPTY' (or 'where to find it in a documentation'). I recommend to post it separately, either here if you want, and/or to Microsoft Forum. Kind regards, — Alexander Bell, Dec 20 '14 at 05:14
@AlexBell you cant demand that the OP accept an answer. Especially given as the OP points out, your initial response didnt address the actual question, the additional response didnt add to that already supplied by Ron Rosenfield — brettdj, Dec 20 '14 at 06:53
@brettdj: If you read carefully my answer, then it clearly states that the reason for that error is NOT Excel loosing the focus (as suggested by Ron), but Erasing the Clipboard Memory. So, why do you still reject my answer? Also, I've suggested the alternative solution optimized for speed and free of that side effects causing the issue. Kind regards, — Alexander Bell, Dec 20 '14 at 07:08
@brettdj: The question is answered, so it should be accepted. Frankly, this simple issue took disproportionally too much time. Best regards, — Alexander Bell, Dec 20 '14 at 07:18
Putting the discussion about answer etiquette to one side, well done for closing out this problem. Regards DB. — brettdj, Dec 21 '14 at 03:49

"Application.Calculation = xlCalculationManual" statement causing run-time error 1004 in VBA Copy-Paste procedure

2 Answers2

Linked

Related