(VBA-Excel) Efficient way to transfer a large range

Question

I was wondering, which would be more efficient for a transfer of a large range of cells (about 100 000 cells - 10 000 x 10) between workbooks or worksheets:
1. Copying and pasting as values:

rng1.Copy
rng2.PasteSpecial xlValues
Application.CutCopyMode=False

2. Passing only the values with .Value:

rng2.Value = rng1.value
'The ranges will be predefined to be the same size

Thanks for the help in advance!

Answer 1

I'm pretty sure your second option is best, but formatting won't be transferred. You may want to consider using Value2 instead, there is a performance improvement over using Value.

rng2.Value2 = rng1.value2

Edit 1

After doing some testing, I'm surprised to see that copy/paste can be faster, about 5 times faster with the testing below, but this is with a mostly empty range. Also, Value2 reaches an out of memory error sooner. That being said, Value2 is cleaner code and it doesn't wipe out your clipboard contents, something to think about.

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub test()
    Dim r1, r2 As Range
    Dim t As Long

    Set r1 = Sheets("S1").Range("A1:Z100000")
    Set r2 = Sheets("S2").Range("A1:Z100000")

    'Method1
    t = GetTickCount
    r1.Copy
    r2.PasteSpecial xlValues
    Debug.Print GetTickCount - t

    'Method 2
    t = GetTickCount
    r2.Value2 = r1.Value2
    Debug.Print GetTickCount - t

End Sub

Edit 2

Previous testing was with a mostly empty range. Filling the range with a Table and filing the table with content shows that Value2 is ~40% faster at handling the full range, but it always handles the full range. I suspect that the difference may be that Copy/Paste has the advantage of being more superficial and thereby realizes a performance gain if empty cells are present.