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.

Parameters

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()
 Worksheets("Range.Copy").Range("B6:F11").Copy
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 _
 Destination:=Worksheets("Destination").Range("B2")
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
 wkbookTarget.Worksheets(1).Range("A1").Select

 'activate the source book
 wkbookThis.Activate
 
 'copy the range from source book
 wkbookThis.Worksheets("Range.Copy").Range("B6:F11").Copy
 
 'paste the data on the target book
 wkbookTarget.Worksheets(1).Range("A1").PasteSpecial
 
 'save the target book
 wkbookTarget.Save
 
 'close the workbook
 wkbookTarget.Close

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