VBA Copy Worksheet

Associated Files Download Links

This tutorial will cover how to copy a Sheet or Worksheet using VBA.

Copy Worksheet to New Workbook

To copy a worksheet to a new Workbook:

Copy ActiveSheet to New Workbook

To copy the ActiveSheet to a new Workbook:

 

Copy Multiple Sheets to New Workbook

To copy multiple Sheets to a new workbook:

 

Copy Sheet Within Same Workbook

We started off by showing you the most simple copy Sheets example: copying Sheet(s) to a new Workbook.  These examples below will show you how to copy a Sheet within the same Workbook. When copying a Sheet within a Worbook, you must specify a location.  To specify a location, you will tell VBA to move the Worksheet BEFORE or AFTER another Worksheet.

Copy Sheet Before Another Sheet

Here we will specify to copy and paste the Sheet before Sheet2

 

Copy Sheet Before First Sheet

Instead of specifying the Sheet name, you can also specify the Sheet position. Here we are copying and pasting a Sheet before the first Sheet in the Workbook.

The newly created Sheet will now be the first Sheet in the Workbook.

vba copy sheet before first sheet

Copy Sheet After Last Sheet

Use the After property to tell VBA to paste the Sheet AFTER another sheet. Here we will copy and paste a Sheet after the last Sheet in the Workbook:

Notice that we used Sheets.Count to count the number of Sheets in the Workbook.

vba copy after last sheet

Move Sheet

You can also move a Sheet within a Workbook using similar syntax. This code will move Sheet1 to the end of the Workbook:

 

Copy and Name Sheet

After copying and pasting a Sheet, the newly created sheet becomes the ActiveSheet. So to rename our new sheet, simply use ActiveSheet.Name:

 

If the Sheet name already exists, the above code will generate an error.  Instead we can use “On Error Resume Next” to tell VBA to ignore naming the Sheet and proceed with the rest of the procedure:

 

Or use our RangeExists Function to test if the Sheet name already exists before attempting to copy the sheet:

 

Copy and Name Sheet Based on Cell Value

You might also want to copy and name a Sheet based on a Cell Value.  This code will name the Worksheet based on the Cell value in A1

 

Copy Worksheet to Another Workbook

So far we’ve worked with copying Sheets within a Workbook. Now we will cover examples to copy and paste Sheets to other Worbkooks. This code will copy a Sheet to the beginning of another workbook:

This will copy a Worksheet to the end of another Workbook.

Notice we replaced 1 with Workbooks(“Example.xlsm”).Sheets.Count to get the last Worksheet.

Copy Worksheet to a Closed Workbook

You might also want to copy a Worksheet to a Workbook that is closed.  This code will open a closed Workbook so that you can copy a Sheet into it.

 

Copy Sheet from Another Workbook Without Opening it

Conversely, this code will copy a Worksheet FROM a closed Workbook without you needing to manually open the workbook.

Notice that in both these examples we disabled ScreenUpdating so the process runs in the background.

 

Duplicate Excel Sheet Multiple times

You can also duplicate an Excel Sheet multiple times by using a Loop.

vba duplicate sheet

You may also like some of this related content...

Advertisements
Automate Excel
Left Menu Icon