Reason for different speeds of code

[teatimecrumpet]

Hi does someone know why I'm getting different speeds for the different portions of code that is basically just copying and pasting all visible cells on a worksheet and then pasting it to another sheet?

thanks,

'this code finishes almost instantaneously
Cells.Select
Selection.Copy
Worksheets("DSR").Range("A1").PasteSpecial xlPasteValues


'this code takes 2 seconds
Cells.Copy Destination:=Sheets("DSR").Range("A1")


'this code takes 10-15 seconds
Columns("A:U").Copy Destination:=Sheets("DSR").Range("A1")

 

[sijpie]

That is not what I get.

I filled a sheet with 350 rows & 52 columns then ran the following code (basically your code with timers inbetween):

Option Explicit
Sub testc()
Dim st As Double, en As Double
Range("A1").Select
st = Timer
Cells.Select
Selection.Copy
Worksheets("DSR").Range("A1").PasteSpecial xlPasteValues
en = Timer
MsgBox en - st
Range("A1").Select
st = Timer

'this code takes 2 seconds
Cells.Copy Destination:=Sheets("DSR").Range("A1")
en = Timer
MsgBox en - st
Range("A1").Select
st = Timer

'this code takes 10-15 seconds
Columns("A:U").Copy Destination:=Sheets("DSR").Range("A1")
en = Timer
MsgBox en - st
End Sub

I then get (as I expected)
0.125 sec for the first and 0.015 sec for 2nd and 3rd method.

Selecting always takes time and so the first method should take longest.

So I don't know why you have very different results. Try again with the macro above to see if it still is the case.

The other thing that may have an effect is if your data contain lots of formulas, as these will need to be recalculated, and that may have something to do with it.

 

[RoryA]

If there's a lot of formatting, then a full copy could well take longer than only copying (or pasting) values.

 

[teatimecrumpet]

Hi,

Thanks for the replies.

Before any of those lines run it repositions columns by cutting and inserting. Couple of colums are added as well.

Then a filter is applied.

There aren't any formulas.

Here is the code above the copying and pasting:

Application.ScreenUpdating = False
ActiveWorkbook.ActiveSheet.Name = "Q_DSR"
ActiveWorkbook.Sheets("Q_DSR").Copy After:=Sheets("Q_DSR")
ActiveSheet.Name = "Preliminary"
Worksheets.Add(After:=Worksheets("Preliminary")).Name = "DSR"

Worksheets("Preliminary").Activate
Columns("A").Insert
Columns("E").Cut
Columns("B").Insert
Columns("E").Cut
Columns("C").Insert
Columns("D").Insert
Columns("M").Cut
Columns("E").Insert
Columns("O").Cut
Columns("F").Insert
Columns("G").Insert
Columns("J").Cut
Columns("I").Insert
Columns("M").Cut
Columns("J").Insert
Columns("K").Insert
'Insert Headers
Range("A1").Formula = "1"
Range("D1").Formula = "2"
Range("G1").Formula = "3"
Range("K1").Formula = "4"

'filter for aggregate amount and fund name
With Sheets("Preliminary")
.AutoFilterMode = False

.Range("$A$1:$U$8000").AutoFilter field:=5, Criteria1:="3"
.Range("$A$1:$U$8000").AutoFilter field:=2, Criteria1:=Array( _
"a", "b", "c", _
"d", "e", "f", _
"g", "h", _
"i", "j"), Operator:=xlFilterValues


.Range("$A$1:$U$8000").AutoFilter field:=9, Criteria1:=Array( _
"Yes", "No"), Operator:=xlFilterValues
End With