Copy a worksheet

The Copy method of the Worksheet object copies the sheet and its contents into an open or new workbook.

Notes
The copied worksheet keeps the Name and CodeName properties of the source worksheet. If the copied worksheet held a worksheet code sheet in a VBA project, that is also carried into the new workbook.

Copy a worksheet into the active or another open workbook

To copy Before a specified worksheet:


Dim ws As Worksheet: Set ws =
Dim wsBefore As Worksheet: Set wsBefore =
ws.Copy Before:=wsBefore

As an example, assuming Book1.xslx is open in Excel, the following code copies the Active sheet from the workbook containing the macro into Book1.xlsx before Sheet1


Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet
Dim wbBefore As Workbook: Set wbBefore = Application.Workbooks("Book1.xlsx")
Dim wsBefore As Worksheet: Set wsBefore = wbBefore.Sheets("Sheet1")
ws.Copy Before:=wsBefore

To copy After a specified worksheet:


Dim ws As Worksheet: Set ws =
Dim wsAfter As Worksheet: Set wsBefore =
ws.Copy After:=wsAfter

Copy a worksheet to a new workbook

Excel creates a new workbook that contains the copied sheet object that contains the copied Worksheet object. The newly created workbook becomes the ActiveWorkbook and contains a single worksheet.


Dim ws As Worksheet: Set ws =
ws.Copy

Copying multiple sheets in one go

An array selection of multiple worksheets can be copied in a similar manner.


wb.Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Copy