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.