VBA to copy all sheets to new workbook except one sheet

[ziadh15]

Hello

I want to copy all sheets in active workbook into a new workbook except one sheet called "Main".

I have written the code but it only copies the sheets that typed inside array.

I have a lot of hidden sheets so will take too much time naming each one.

--> I basically want to select all sheets except "Main" and copy them to new workbook.

Sub SaveAs()

Dim DataWorkbook As Workbook
Dim dt As String
Dim WorkbookName As String
Dim fileToSaveAs As Variant
Dim s As Worksheet

dt = Format(CStr(Now), "dd_mm_yyyy")
Set DataWorkbook = ActiveWorkbook


DataWorkbook.Sheets(Array("Instructions", "Collars")).Copy
WorkbookName = "c:\temp\Fishing Diagrams for StabilDrill_" & dt
fileToSaveAs = Application.GetSaveAsFilename(InitialFileName:=WorkbookName)

If fileToSaveAs <> False Then
ActiveWorkbook.SaveAs Filename:=fileToSaveAs, FileFormat:=52
End If

End Sub

DataWorkbook.Sheets(Array("Instructions", "Collars")).Copy
This is where I am stuck. The rest works fine

 

[JLGWhiz]

Would this work?

Sub SaveAs()
Dim DataWorkbook As Workbook
Dim dt As String
Dim WorkbookName As String
Dim fileToSaveAs As Variant
Dim s As Worksheet
dt = Format(Date, "dd_mm_yyyy")
Set DataWorkbook = ActiveWorkbook
WorkbookName = "c:\temp\Fishing Diagrams for StabilDrill_" & dt.xlsm
fileToSaveAs = Application.GetSaveAsFilename(InitialFileName:=WorkbookName)
    If fileToSaveAs <> False Then
        DataWorkbook.SaveAs Filename:=fileToSaveAs, FileFormat:=52
        Application.DisplayAlerts = False
        ActiveWorkbook.Sheets("Main").Delete
        Application.DisplayAlerts = True
    End If
End Sub

Probably just as fast to manually do a saveas and then delet sheet "Main".