Copy a worksheet
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