VBA Copy Destination (Copy Range to Another Sheet)

In this Article

Copy to Existing Sheet
Copy to New Sheet
Copy to Existing Workbook
Copy to New Workbook

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:

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.

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.

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:

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.

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.

Sub CopyAndPasteOpenWorkbook()
 Range("A1:D9").Select
 Selection.Copy
 Workbooks.Open Filename:= "C:\ExcelFiles\CombinedBranches.xlsx"
 Sheets.Add After:=ActiveSheet
 ActiveSheet.Paste
End Sub

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.

Sub CopyAndPasteNewWorkbook()
 Range("A1:D9").Select
 Selection.Copy
 Workbooks.Add
 ActiveSheet.Paste
End Sub