Which is a faster copying method? Excel VBA

Question

I'm going to be repeating part of a VBA module many times and I want to make sure I'm doing this the most efficient way. Most efficient meaning the quickest loading time.

Basically, I'm copying data from a range on wb2 and pasting it to a wb1 destination.

The first method seems easier as it's shorter:

wb2.Sheets(1).Range(Cells(2, TrpCdBLCol), Cells(100, TrpCdBLCol)).Copy wb1.Sheets("BL Import").Cells(2, TrpCdCol)

The second method I declare two variables as ranges, set them, and the copy

Dim CopyRange As Range, Dest As Range
    Set CopyRange = wb2.Sheets(1).Range(Cells(2, TrpCdBLCol), Cells(100, TrpCdBLCol))
    Set Dest = wb1.Sheets("BL Import").Cells(2, TrpCdCol)
    CopyRange.Copy Dest

Is there any difference or is one way better than the other? Thanks!

Consider running some performance tests? This question could easily be answered by trial, error, and good debugging. (And it's not likely you'll notice huge performance gains unless you have some exceptionally large ranges, etc.) From a style, readability (and maintainability) perspective, the second method is more explicit and legible. — David Zemens, Aug 13 '15 at 03:45
Alternatively: `wb2.Sheets(1).Cells(2, TrpCdblCol).Resize(99).Copy wb1.Sheets("BL Import").Cells(2, TrpCdCol)` ... there are *many* ways to express essentially the same "copy/paste" operation. — David Zemens, Aug 13 '15 at 03:46

score 3 · Accepted Answer · edited May 23 '17 at 12:30

You can get identical contents from range1 to range2 without a copy and paste by:

Setting the values directly in Range2 from Range1 (this method copies values, and can be used to calculate formulae, but not formats)
Using a variant array (which adds a step)

On my testing the first option ran in 2/3 the time of the second - which would be due to the additional step.

Update: Efficient techniques

On using Long rather than Integer, Why Use Integer Instead of Long?
On using Value2 rather than Value, see post from Charles Williams here
If you are manipulating data and writing it back to a sheet use a variant array, not a range object. If you are working with strings then hereis a one stop reference on efficient coding

Option 1

Sub Recut1()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim TrpCdBLCol As Long

TrpCdBLCol = 1
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)

vArr = ws1.Range(ws1.Cells(2, TrpCdBLCol), ws1.Cells(100, TrpCdBLCol))
ws2.Range(ws2.Cells(2, TrpCdBLCol),ws2.Cells(100, TrpCdBLCol)).Value2 = vArr

End Sub

Option 2

Sub Recut2()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim TrpCdBLCol As Long
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
TrpCdBLCol = 1

ws2.Range(ws2.Cells(2, TrpCdBLCol), ws2.Cells(100, TrpCdBLCol)).Value2 = ws1.Range(ws1.Cells(2, TrpCdBLCol), ws1.Cells(100, TrpCdBLCol))

End Sub

Thanks Brett, appreciate you add a figure to how much quicker one process is. Any reason you set TrpCdBLCol as Long as opposed to as an Integer? I had been setting them to Integers, but I'm still learning about all this — arbitel, Aug 13 '15 at 13:42
option 1 is ideal solution both for readability and performance +1 (and the details) — paul bica, Aug 14 '15 at 02:06

score 2 · Answer 2 · answered Aug 13 '15 at 00:23

The only (very slight) difference in performance between your 2 options is that the second one will perform the extra operation of creating 2 more Range objects

Main consideration related to performance when copying data is if you need the formatting of cells (colors, fonts, etc) which requires a lot of work behind the scenes but it's preferable to doing it yourself. If you don't need the formatting you immediately gain a significant boost in performance

For maintainability the second option is better, but you should also use explicitly references to all objects (including Cells()), and bypass the clipboard entirely. Also, there is no need to repeat the code: create a shared sub that takes 2 worksheets as parameters

.

Option Explicit

Public Sub copyData(ByRef wsFrom As Worksheet, ByRef wsTo As Worksheet)

    With wsFrom
        Set copyRange = .Range(.Cells(2, TrpCdBLCol), .Cells(100, TrpCdBLCol))
    End With

    With wsTo
        Set destRange = .Range(.Cells(2, TrpCdBLCol), .Cells(100, TrpCdBLCol))
    End With

    destRange.Value2 = copyRange.Value2

End Sub

.

and use this line to call it from whatever worksheet you need:

copyData wb2.Worksheets(1), wb1.Worksheets("BL Import")

Thanks a lot for answering my question and providing a quick solution for repeating this process easily. Appreciate it! — arbitel, Aug 13 '15 at 13:39
Had missed this response which covered the same area I did later in Option 2. +1 — brettdj, Aug 14 '15 at 01:59

score 0 · Answer 3 · answered Aug 12 '15 at 23:12

0

I would probably skip the copying altogether and use an array. For example:

Sub CopyWithoutCopying()
Dim MyArr As Variant
    MyArr = Application.Transpose(Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row))
    Sheets("Sheet2").Range("A1").Resize(UBound(MyArr), 1) = Application.Transpose(MyArr)
End Sub

This will copy the data in column A to a sheet called Sheet2 column A

answered Aug 12 '15 at 23:12

Dan Donoghue

6,056
2
18
36

Transpose isn't necessary for using an array. Also works on a 1D range only (so it while it works for this particular question, wont work for a range with multiple rows and multiple columns). – brettdj Aug 13 '15 at 03:28
I was transposing to an array due to the resize as I was using a dynamic range and needed the ubound of the array to do it. If I don't transpose it, it errors and MyArr is empty. I spose taking your comment though (Thanks I didn't actually know this before I thought it HAD to use transpose) I could do it like this `Sheets("Sheet2").Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.count).end(xlup).row).value = Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.count).end(xlup).row).value` – Dan Donoghue Aug 13 '15 at 03:43

Which is a faster copying method? Excel VBA

3 Answers3

Linked