Cut & paste with manual calculation

[crewaustin66]

Does anyone know why I get a 1004 Paste method of Worksheet class failed when I try to cut&paste in VBA with Application.Calculation set to manual? Being able to do this would optimize my macro significantly.

Thanks,
Crew

Application.Calculation = xlCalculationManual

                If ref2Row > 0 Then
                    If ref2Row = ref1Row Then
                        Range("B" & curRow + 1).Select
                    Else
                        Range("B" & curRow).Select
                    End If
                    
                    ActiveSheet.Paste
                Else
                    If ref1Row > 0 Then
                        Range("B" & curRow).Select
                DoEvents
                        'ActiveSheet.Paste
                        ActiveSheet.Paste
                    End If
                End If

 

[VoG]

You will need to post the code.

Please use code tags:


[code]
paste your code here
[/code]

 

[crewaustin66]

You will need to post the code.

Please use code tags:


[code]
paste your code here
[/code]

The code is there already. Perhaps you looked at my post before I edited it.

 

[VoG]

Is the sheet protected?

What is the code to copy? Please post the complete code - we are not mind readers.

 

[crewaustin66]

For whatever reason, I can't paste the code into the board. Odd. It's a simple Range(copyRange).Select followed by a Selection.Copy. I do this before my import data loop and then just .paste the block of cells in the loop before I write the data over it from the imported data. When I do the .paste with Application.Calculation = xlCalculationManual the .paste fails with the

 

[VoG]

I don't think that this is anything to do with manual calculation.

If you copy a range then open another workbook to paste to this action will clear the clipboard so there is nothing to paste.This scenario would generate a 1004 error.

The solution in this case would be to open the second workbook and then do the copy and paste.

 

[crewaustin66]

I don't think that this is anything to do with manual calculation.

If you copy a range then open another workbook ...

I copy from the same sheet that I am pasting to. Adding in the manual calculation definitely triggers the 1004.

 

[VoG]

OK, I was wrong and you are right - see http://www.mrexcel.com/forum/excel-questions/459793-stop-vbulletin-clearing-clipboard.html

I think that you need to switch calculation to manual before doing the copy.

 

[crewaustin66]

Thank you for the topic link. That got it!