Excel VBA Copy Range and Paste Method

This Excel VBA tutorial explains how to copy Range and paste data using VBA

Excel VBA Copy Range Method

Range.Copy Method is a very convenient Method to copy and paste Range to destination in one line of code. All the formatting will be copied and pasted.

You can copy a Range, a column, a row.

Syntax

Range.Copy(Destination)

Destination is optional. If not specified, Excel copies the Range to the Clipboard.

In some cases, we may just want to copy to Clipboard and then use Paste/PasteSpecial Method to paste, but in terms of performance, it is better not to use Clipboard.

Example – Copy Range and paste to another worksheet

The below code copy Sheet1 A1 to Sheet2 B1.

Public Sub cpy()
    Sheets("Sheet1").Range("A1").Copy (Sheets("Sheet2").Range("B1"))
End Sub

If there are more than one Range to copy, you just need to specific the first Range of the destination.

Public Sub cpy()
    Sheets("Sheet1").Range("A1:B1").Copy (Sheets("Sheet2").Range("B1"))
End Sub

Example – copy row

The below code copy from Sheet1 row 1:2 to Sheet2 row 6.

Note that if you copy a row to destination, the destination Range must be a Row or a Range in column A.

Public Sub cpyRow()
    Sheets("Sheet1").Rows("1:2").Copy (Sheets("Sheet2").Range("A6"))
End Sub

Public Sub cpyRow()
    Sheets("Sheet1").Rows("1:2").Copy (Sheets("Sheet2").Rows("6:6"))
End Sub

Public Sub cpyRow()
    Sheets("Sheet1").Rows("1:2").Copy (Sheets("Sheet2").Rows("6:7"))
End Sub

Example – copy row and paste to new inserted row

Public Sub cpy_insert()
    ActiveCell.EntireRow.Copy
    ActiveCell.EntireRow.Insert
End Sub

Example – copy column

The below code copy Sheet1 column A:B to Sheet2 column B:C (because it pastes at B1).

Public Sub cpyColumn()
    Sheets("Sheet1").Columns("A:B").Copy (Sheets("Sheet2").Range("B1"))
End Sub

Example – copy multiple Range to a new Workbook

In case the copied Columns are not adjacent to each other, Set a Range to combine those Columns (or Rows).

The below code copy range1 and then paste to A1 of new workbook.

Public Sub newWB()
     Set range1 = Range("A:B, E:F")
     range1.Copy
     Set newbook = Workbooks.Add
     Range("A1").PasteSpecial
 End Sub

Note that there are two Paste related Methods:

– Range PasteSpecial Method

– Worksheet Paste Method

I prefer to use PasteSpecial Method because it allows optional parameter to paste as value, and Paste Method is easier to cause Run-time error ‘1004’.

PasteSpecial Method without argument copies the format as well.