How To Copy A Range Of Cells In Excel Using VBA

There are many ways to achieve the same objective in Excel, in this instance copying a range of cells to a new spreadsheet location.

The spreadsheet above represents the situation after the code has been executed.

Before execution the range "F1 through "H6" would not contain any data.

In this example I will copy the cells in vba by creating a range object "rngSource" to refer to the cells in range "A1" through "C6".

I will then create another range object "rngTarget" to refer to cell "F1".

To eliminate any confusion the object "cdeData" on line 4 in the code listing below (which you can also see in the properties window to the right of this paragraph) refers to the "code sheet" rather that the Tab name in this instance "The Data".

Line 6 below executes the copy command on the range object and passes it the destination parameter which in this instance is "rngTarget" or cell "F1".

That’s it we have copied the source range to the target range, job done!

Line 7 then cleans things up by destroying the range objects as they are no longer required.

01  Sub copyCells()
02    Dim rngSource As Range, rngTarget As Range
03    ' Using Clipboard
04    Set rngSource = cdeData.Range("A1").currentRegion
05    Set rngTarget = cdeData.Range("F1")
06    rngSource.Copy rngTarget
07    Set rngSource = Nothing: Set rngTarget = Nothing:
08  End Sub 

How To Copy An Excel Range Without Using Copy

Another way of achieving the same result and the way I do it in most, but not all cases is to write the contents of the source range to the target range without using copy.

This is done by makeing the target range the same dimensions as the source range and then simply setting the value property of the source range to the target range.

Because both ranges are objects you can query their dimensions and then use the resize method to change the size of the target range in this instance.

01  Sub copyCells()
02    Dim rngSource As Range, rngTarget As Range
03    
04    Set rngSource = cdeData.Range("A1").currentRegion
05    Set rngTarget = cdeData.Range("F1")
06    ' Direct Writing
07    rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
08
09    Set rngSource = Nothing: Set rngTarget = Nothing:
10  End Sub 

Line 7 above resizes the target range and writes the range to cells "F1" through "H6" in one clean readable line.

Don’t forget to sign up in the yellow box above to get notified about future articles and videos on VBA and other businessprogrammer topics.