Studies show that good grammar leads to more promotions and higher salaries. Write with Grammarly

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
How to Copy Worksheets in Excel VBA?

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
How to Copy Worksheets in Excel VBA?

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")
How to Copy Worksheets in Excel VBA?

Copy Sheet Before First Sheet

To copy sheet before the first sheet,

Code:

Sheets("Sheet1").Copy Before:=Sheets(1)
How to Copy Worksheets in Excel VBA?

Copy Sheet After Last Sheet

To copy the sheet after the last sheet,

Code:

Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
How to Copy Worksheets in Excel VBA?