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.
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.
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”.
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”).
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
Set wkbookTarget = Nothing
Set wkbookThis = Nothing