VBA Copy Destination (Copy Range to Another Sheet)
This article will demonstrate how to use VBA to Copy a Range of Cells to Another Sheet or Workbook.
For more information, read our VBA Copying and Pasting Tutorial.
Copy to Existing Sheet
To copy a range of cells from one sheet to another sheet that already exists we can use the following code:
|
1 2 3 4 5 6 |
Sub CopyAndPaste ActiveSheet.Range("A1:D10").Select Selection.Copy Sheets("Sheet2").Select ActiveSheet.Paste End Sub |
This will copy the information stored in the active sheet range A1:D10, and paste it into an existing Sheet2. As we have not specified the range to select in Sheet 2, it will automatically paste it to Range(“A1”). It will also paste any formatting into Sheet 2 that was in the range in Sheet 1.
If we wish to paste it to a different location in Sheet 2, we can select the starting cell or range to paste to. The code below will paste the information starting in cell E2.
|
1 2 3 4 5 6 7 |
Sub CopyAndPasteToRange ActiveSheet.Range("A1:D10").Select Selection.Copy Sheets("Sheet2").Select Range("E1").Select ActiveSheet.Paste End Sub |
To just paste the values into Sheet 2 and not include the formatting, we can use the following code. Once again, we do not have to specify the range to page to if we wish to paste to cell A1.
|
1 2 3 4 5 6 |
Sub CopyAndPasteValues ActiveSheet.Range("A1:D10").Select Selection.Copy Sheets("Sheet2").Select Selection.PasteSpecial Paste:=xlPasteValues End Sub |
Copy to New Sheet
To copy and then paste into a new sheet, we can use the following code:
|
1 2 3 4 5 6 |
Sub CopyAndPasteNewSheet ActiveSheet.Range("A1:D10").Select Selection.Copy Sheets.Add After:=ActiveSheet ActiveSheet.Paste End Sub |
To just copy values, we can once again use xlPasteValues.
Copy to Existing Workbook
To copy and paste to a different workbook entirely, we can either have both workbooks already open, or we can use the code to open another workbook, and then paste into that workbook.
This code below copies to an existing workbook that is already open.
|
1 2 3 4 5 6 7 |
Sub CopyAndPasteExistingBook Range("A1:D10").Select Selection.Copy Windows("CombinedBranches.xlsx").Activate Sheets.Add After:=ActiveSheet ActiveSheet.Paste End Sub |
This code below will copy and paste into a new sheet in a 2nd workbook that will be opened by the code.
|
1 2 3 4 5 6 7 |
Sub CopyAndPasteOpenWorkbook Range("A1:D9").Select Selection.Copy Workbooks.Open Filename:= "C:\ExcelFiles\CombinedBranches.xlsx" Sheets.Add After:=ActiveSheet ActiveSheet.Paste End Sheet |
TIP: replace the name of the file in the Workbooks.Open argument with your own file name!
Copy to New Workbook
We can also Copy and Paste to a new Workbook.
|
1 2 3 4 5 6 |
Sub CopyAndPasteNewWorkbook Range("A1:D9").Select Selection.Copy Workbooks.Add ActiveSheet.Paste End Sub |
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!
