Copy all sheets from current workbook to new workbook except the first sheet

Question

I have a macro-based workbook where I run multiple processes and all the results are stored in the same XLSM workbook. I need to store all the output sheets i.e all the sheets except the sheet(1) to a new workbook and save it as xlsx ( macro-free file )

I have written a very simple code to copy a specific sheet and rename it to xlsx. However, I need to copy all sheets except first one and save it as a macro-free workbook ( so that no VBA code present in output file )

Sub copy()

   ThisWorkbook.Sheets("Sheet1").Copy
    ActiveWorkbook.SaveAs "C:\Output.xlsx", FileFormat:=51
End Sub

Not tested, but I think it would be easier to copy the file itself, open it and delete first worksheet. — Foxfire And Burns And Burns
Imagine 4 sheets (1, 2, 3, 4): Do you want 2 in file2, 3 in file3 and 4 in file4 or do you want one file with 2, 3, 4? And what do you mean by first sheet? The sheet in first position of the tab bar Sheets(1) or the sheet named Sheets("Sheet1") note that these can be different sheets. — Pᴇʜ

Jehhred Jehhred · Accepted Answer · 2020-05-14T13:33:26

This takes all sheets starting from the second sheet to the end and copies them into a new workbook, then finally deletes the 'Sheet1' sheet that excel automatically uses as the first sheet. Personally, I think it's probably much easier to duplicate the current workbook and then just delete the first sheet.

Sub copy()

Dim NewWkb As Workbook
Dim xWkb As Workbook
Dim x As Integer

With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

Set xWkb = ThisWorkbook
Set NewWkb = Workbooks.Add

For x = 2 To xWkb.Worksheets.Count
    xWkb.Worksheets(x).copy after:=NewWkb.Worksheets(NewWkb.Worksheets.Count)
Next x

NewWkb.Worksheets("Sheet1").Delete

'Plenty of different ways to save a file, here's one way for a Mac
NewWkb.SaveAs Filename:="C:\Users\Folder\newfile", FileFormat:= 51

With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

End Sub

Copy all sheets from current workbook to new workbook except the first sheet

1 Answers