How to Copy Worksheets in Excel VBA?

In this post, you’ll learn how you can copy worksheets in your Excel spreadsheet using Excel VBA.

Copy Worksheets in Excel VBA

To copy the sheets, use .copy command in your code. Let us see the different operations that we can perform using the .copy command.

Copy Worksheet to New Workbook

To copy a worksheet to a new Workbook, enter the following code in the Visual basics tab.

Code:

Sheets("Sheet1").Copy

The sheet, Sheet 1 was copied to a new workbook.

Copy ActiveSheet to New Workbook

To copy the ActiveSheet to a new Workbook,

Code:

ActiveSheet.Copy

Here, the active sheet was Sheet 3, which was copied to a new workbook.

Copy Sheet Within Same Workbook

Now, let us see how to copy and paste worksheets within the same workbook.

There are three options to copy paste the worksheet in the same workbook.

• Before a sheet
• Before the first sheet
• After Last sheet

Copy a sheet before a worksheet

To copy a sheet before a worksheet

Code:

Sheets("Sheet1").Copy Before:=Sheets("Sheet2")

Copy Sheet Before First Sheet

To copy sheet before the first sheet,

Code:

Sheets("Sheet1").Copy Before:=Sheets(1)

Copy Sheet After Last Sheet

To copy the sheet after the last sheet,

Code:

Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)