Option Explicit
Sub testCopy_speed()
Dim R1 As Range, r2 As Range
Set R1 = ThisWorkbook.Sheets(1).Range("A1:Z1000")
Set r2 = ThisWorkbook.Sheets(2).Range("A1:Z1000")
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
Dim t As Single
Dim i&, data(), Rg As Range
ReDim data(R1.Rows.Count, R1.Columns.Count)
For Each Rg In R1.Cells: Rg = Rnd()*100: Next Rg
R1.ClearContents
R1.ClearFormats
r2.ClearContents
r2.ClearFormats
'For Each Rg In R1.Cells: 'if you do this too often , you'll get an error
' With Rg
' .Value2 = Rnd() * 100
' .Interior.Color = Rnd() * 65535
' '.Font.Color = Rnd() * 65535
' End With
'Next Rg
t = Timer
For i = 1 To 100
'r2.Value2 = R1.Value2 '1,71 sec
'R1.Copy r2 '0.74 sec <<<< Winer , but see NOTE.
'data = R1.Value2: r2.Value2 = data '1.78 sec
'For Each Rg In R1.Cells: r2.Cells(Rg.Row, Rg.Column).Value2 = Rg.Value2: Next Rg '54 seconds !!
Next i
Erase data
Set R1 = Nothing
Set r2 = Nothing
Set Rg = Nothing
Debug.Print Timer - t
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub