Why is the VBA Range.copy Method Useful?

The Range.copy method is simply used to copy a range into a specified range or the clipboard. This is commonly used when you have specific data range within the worksheet that you want to copy. It can also be used to copy a formula from a certain column into another column.


Name Required/Optional Data Type Description
Destination Optional Variant Specifies the new range to which the specified range will be copied. If this argument is omitted, Microsoft Excel copies the range to the Clipboard.


Sample code # 1: Copying a range to the clipboard – Specify the start column & row and the last column & row (ex. “B6:F11”). The image below shows that the data covered by the copy method being highlighted with a dotted box.

Sub RangeCopy()
End Sub

Sample code # 2: Copying a range with a destination parameter – Specify the worksheet name and column & row where the copied range will be inserted. In the sample code below, notice that the data we’ve copied from the first sample code was copied or inserted into another worksheet named “Destination”.

Sub RangeCopy()
 Worksheets("Range.Copy").Range("B6:F11").Copy _
End Sub

Sample code # 3: Copying a range from one workbook to another workbook – The sample below will copy the specified range of data into another workbook. As precondition, it is necessary to have the target workbook file exist in the directory where the program is set to open and copy the data (ex. “c:\Jbujaue\Range.copy.xlsx”).

Sub RangeCopy()
 Dim wkbookTarget As Workbook 'workbook where the data is to be pasted
 Dim wkbookThis As Workbook 'workbook from where the data is to be copied
 Dim strSheetName As String 'name of the source worksheet
 'Specify to the current active workbook (the source book)
 Set wkbookThis = ActiveWorkbook
 'get the active sheetname of the workbook
 strSheetName = ActiveSheet.Name
 'open a workbook that has same name as the sheet name
 Set wkbookTarget = Workbooks.Open("C:\JBujaue\" & strSheetName & ".xlsx")
 'select cell A1 on the target book

 'activate the source book
 'copy the range from source book
 'paste the data on the target book
 'save the target book
 'close the workbook

 'activate the source book again
 'clear memory
 Set wkbookTarget = Nothing
 Set wkbookThis = Nothing
End Sub


Leave a Reply

Your email address will not be published. Required fields are marked *