Save multiple sheets in a new workbook

[Dave_george]

I have a workbook with the multiple sheets (Sheet1, Sheet2 .. Sheet 5) and I want to create a macro assigned button to Save as a new work book containing only Sheet 1, Sheet 2 and Sheet3. On clicking the Macro assigned button the Save as dialogue box should pop up allowing the user to select destination and also optionally a new file name. I am also saving the file as an .xlsx format. Can anyone help me with the code?

 

[Dave_george]

I have got a code that does this however it also insets a blank sheet called sheet1.

Sub SaveSelectedSheetsAsNewWorkbook()
    Dim wb As Workbook
    Dim newWb As Workbook
    Dim ws As Worksheet
    Dim savePath As String
    
    ' Set the save path and filename
    savePath = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx")
    
    ' Exit if user cancels the save dialog
    If savePath = "False" Then Exit Sub
    
    ' Create a new workbook
    Set newWb = Workbooks.Add
    
    ' Loop through the selected sheets and copy them to the new workbook
    For Each ws In ThisWorkbook.Sheets(Array("Sheet6", "Sheet2"))
        ws.Copy After:=newWb.Sheets(newWb.Sheets.Count)
    Next ws
    
    ' Save and close the new workbook
    newWb.SaveAs savePath
    newWb.Close
    
    ' Clean up
    Set newWb = Nothing
End Sub

 

[NoSparks]

Put a breakpoint on the For-Each line and when the code stops there have a look at the workbook that was just added.
A new workbook automatically has a sheet1.
The sheet could be hidden or deleted prior to saving.

 

[NoSparks]

Or maybe this will do

Sub SaveSheetsAsNewWorkbook()
    
    Dim savePath As String
    
    ' Set the save path and filename
    savePath = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx")
    
    ' Exit if user cancels the save dialog
    If savePath = "False" Then Exit Sub
    
    ThisWorkbook.Sheets(Array("Sheet6", "Sheet2")).Copy
    
    ' Save and close the new workbook
    ActiveWorkbook.SaveAs savePath
    ActiveWorkbook.Close

End Sub

 

[Dave_george]

Or maybe this will do

Sub SaveSheetsAsNewWorkbook()
   
    Dim savePath As String
   
    ' Set the save path and filename
    savePath = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx")
   
    ' Exit if user cancels the save dialog
    If savePath = "False" Then Exit Sub
   
    ThisWorkbook.Sheets(Array("Sheet6", "Sheet2")).Copy
   
    ' Save and close the new workbook
    ActiveWorkbook.SaveAs savePath
    ActiveWorkbook.Close

End Sub

Thank you. This worked.