Copy/paste through macro really slow

Alakin

New Member
Joined
Oct 10, 2014
Messages
2
Hi all,
I'm having trouble to copy paste cells through macro.
I'm coping/pasting both cells's formulas and format. The number of cells is something like 120 (a rectangle of 2 rows and 60 columns). The formulas are for some cells excel functions and for others macro functions.
When I copy/paste these excel through the GUI (ctrl+c/ctrl+v or copy/paste) it is really fast, like instantaneus, but if I do it through macro Sub is really slow (it takes something like between 30/60 seconds to do it). If I use the debug (F8 command) I can see that after the copy/paste action, it starts running the macro functions i wrote and that are called from the cells. It seems like this is what takes times.

I tried adding Application.Calculation = xlcalculationmanual and Application.Calculation = xlcalculationautomatic at the beginning and the end of the Sub respectively. But it doesn't change a lot since it recalculates everything at the end. I'm already using Application.ScreenUpdating = False.

What I don't understand is why it is really fast if I copy/paste through GUI and why it is so slow through macro.

I'm using excel 2010.
I also tested the worksheet on excel 2013 and it doesn't seem to have this problem even though it recalculates everything anyway.

Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Welcome to the board. There could be several reasons why it's slow through the code, is your code selecting the cell to copy and then selecting the cell to paste, before pasting? That can slow the process alot. Can you paste your code please?
 
Upvote 0
Thanks for the reply. This is the code but as I said everything is fast, it's the last line "Application.Calculation = xlCalculationAutomatic" which slow down the macro.
Besides it's not even always slow, once every 7/8 times it copies/pastes everytying fast. I don't understand if it could be related to excel clipboard or something else...

Sub AddLink()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect Password:=psw
Cl = "AW"
LRD = LastRowData(Cl)
idx = Range("BX4").Value2

Rows(LRD + 2 & ":" & LRD + 3).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("A" & idx, "BR" & idx + 1).Copy Destination:=Range("A" & LRD + 1)
Application.CutCopyMode = False

For Each cell In Range("A" & LRD + 1, "BR" & LRD + 2)
If cell.Interior.Color = RGB(175, 255, 175) Then
cell.MergeArea.ClearContents
End If
Next

Range("A" & LRD + 1).Select
Selection.ClearContents

ActiveSheet.Protect Password:=psw
Application.Calculation = xlCalculationAutomatic
End Sub
 
Last edited:
Upvote 0
You only have a single copy and paste operation in your code, I'm not sure that alone is significantly slow or the cause of the problem. It may be the loop and working with merged cells, something VBA is not recommended for. It may also be the custom functions/macros in those cells that need optimising. See if the following makes a difference and if not, then I suggest reviewing the rest of your code:
Code:
Sub AddLink()

Const Cl As String = "AW"
Dim cell As Range, idx As Long, LRD As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

With ActiveSheet
    .Unprotect Password:=psw
    idx = .Range("BX4").Value2
    LRD = LastRowData(Cl)
    .Rows(LRD + 2 & ":" & LRD + 3).Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
    .Range("A" & LRD + 1).Resize(2, .Range("BR1").Column).Value = .Range("A" & idx, "BR" & idx + 1).Value
    For Each cell In .Range("A" & LRD + 1, "BR" & LRD + 2)
        If cell.Interior.Color = RGB(175, 255, 175) Then cell.MergeArea.ClearContents
    Next cell
    .Range("A" & LRD + 1).Select
    .Protect Password:=psw
End With

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationManual
End With

End Sub
 
Upvote 0
You're code is doing more than just a copy/paste.

They Copy/Paste itself as you said only effects 2 rows and 60 columns.
So when you do it by hand, that's all that is effected.

But the macro ALSO Inserts 2 rows, AND clears contents of some cells.
That effects the ENTIRE WORKBOOK, any formulas you have anywhere in the book that refers to any cell below where that row was inserted or had it's contents cleared..
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top