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
OR
Public Sub cpyRow()
Sheets("Sheet1").Rows("1:2").Copy (Sheets("Sheet2").Rows("6:6"))
End Sub
OR
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.
