Macro 45: Copy and Paste a Range
One of the basic data manipulation skills you’ll need to learn is copying and pasting a range of data.
It’s fairly easy to do this manually. Luckily, it’s just as easy to copy and paste via VBA.
How it works
In this macro, we use the Copy method of the Range object to copy data from D6:D17 and paste to
L6:L17. Note the use of the Destination argument. This argument tells Excel where to paste the
data.
Sub Macro45a()
Sheets(“Sheet1”).Range(“D6:D17”).Copy _
Destination:=Sheets(“Sheet1”).Range(“L6:L17”)
End Sub
When working with your spreadsheet, you likely often have to copy formulas and paste them as
values. To do this in a macro, you can use the PasteSpecial method. In this example, we copy the
formulas F6:F17 to M6:M17. Notice that we are not only pasting as values using xlPasteValues,
but we are also using xlPasteFormats to apply the formatting from the copied range.
Sub Macro45b()
Sheets(“Sheet1”).Range(“F6:F17”).Copy
Sheets(“Sheet1”).Range(“M6:M17”).PasteSpecial xlPasteValues
Sheets(“Sheet1”).Range(“M6:M17”).PasteSpecial
xlPasteFormats
End Sub
Keep in mind that the ranges specified here are for demonstration purposes. Alter the
ranges to suit the data in your worksheet.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
