Return to VBA Code Examples

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:

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.

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.

Copy to New Sheet

To copy and then paste into a new sheet, we can use the following code:

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.

VBACopyPaste BetweenWorkbooks

This code below will copy and paste into a new sheet in a 2nd workbook that will be opened by the code.

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.


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! vba save as

Learn More!