Save As entire workbook except certain sheets

[excelnoobhere]

Hello all,

New to excel here and I could use a little help, I have the following code below that has a button set up to save the entire workbook at a desired location with a desired name. It works well and all however, there are two pages that are in the workbook that I don't want them to save in the new workbook. they are titled "Main" and "template" how can i go about this?
thanx in advance



Sub SaveWorkbook()




MsgBox ("You will now be prompted to save your file, after naming the file click 'Save' then 'Yes'") 'Notifies User
savename = Application.GetSaveAsFilename(fileFilter:="Exel Files (*.xlsx), *.xlsx")
ActiveWorkbook.SaveAs Filename:=savename, FileFormat:=51 'Something iswrong

End Sub

 

[excelnoobhere]

Sure, give this a try.

Dim ws As Worksheet
Dim savename As String
Dim cnt As Long
Dim arrSheets()

    With ActiveWorkbook
    
        ReDim arrSheets(1 To .Sheets.Count)
            For Each ws In .Sheets
            Select Case ws.Name
                Case "Main", "Template"
                    ' do nothing
                Case Else
                    cnt = cnt + 1
                    arrSheets(cnt) = ws.Name
            End Select
        Next ws
        
        ReDim Preserve arrSheets(1 To cnt)
        
        ' copy sheets to new workbook
        .Sheets(arrSheets).Copy
    
    End With
    
    MsgBox ("You will now be prompted to save your file, after naming the file click 'Save' then 'Yes'") 'Notifies User
    
    savename = Application.GetSaveAsFilename(fileFilter:="Exel Files (*.xlsx), *.xlsx")
    
    If savename <> "False" Then
        ' save and close new workbook
        With ActiveWorkbook
            .SaveAs Filename:=savename, FileFormat:=51  'Something iswrong
            .Close SaveChanges:=False
        End With
    End If

I appreciate the help this worked perfectly!
Thank you

 

[excelnoobhere]

How could they, if you include all four steps in your macro/VBA code?
People cannot elect to run just "part" of your code. It is an "all or nothing" proposition.
By doing the steps I outlined in the code, once the copy without the sheets is saved to the other location, the Excel file is immediately closed, so that version without the sheets will not be open for them to save anywhere else.

The only way that they could overwrite the original with a copy without the sheets is to open the copy saved to the other location, and then elect to save that to the original location.
But any way you do it, nothing will prevent a user from doing that (unless you were to make the original copy read-only).

I tested the code, and it does closes the "Main" workbook and it works fine. The only problem I have is that I don't want them closing the "main" workbook right after saving, I still have some buttons and functions I want them to follow after saving the new workbook.

 

[Joe4]

If Norie's code does all that you want, then I would just recommend using that.