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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,684
Office Version
  1. 365
Platform
  1. Windows
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?
 

Alakin

New Member
Joined
Oct 10, 2014
Messages
2
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:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,684
Office Version
  1. 365
Platform
  1. Windows
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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,029
Messages
5,526,340
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top