VBA copy range

There are a few ways you can copy a range in VBA. For example, you can copy only values or entire cells, including formatting.

Copy a range

The easiest way to copy a bunch of cells is by entering ranges inside the code. The following code will copy the range A1:B8 to D1:E8.

Sub CopyRange()
    Range("A1:B8").Copy Range("D1:E1")
End Sub

Copy a range to another worksheet

In order to use the following code, you have to create two sheets with the following names: Sheet1 and Sheet2 (or any other, just don’t forget to change it inside the code). The following code copies cells from the range and pastes it into the same range on the other sheet.

Sub CopyRangeBetweenSheets()
    Sheets("Sheet1").Range("A1:B8").Copy Sheets("Sheet2").Range("A1:B8")
End Sub

Copy a range to another worksheet without formatting

So far we copied entire cells. If you want to copy just values, you can use the following code.

Sub CopyRange()
    Range("D1:E8").Value = Range("A1:B8").Value
End Sub

The result will look like this.

Posted in vba