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.
|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() 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