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

 

[Norie]

You can delete the 2 worksheets before saving.

    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")

    Application.DisplayAlerts =False
    ActiveWorkbook.Sheets(Array("Main", "Template)).Delete
    Application.DisplayAlerts = True

    ActiveWorkbook.SaveAs Filename:=savename, FileFormat:=51 'Something iswrong

End Sub

 

[Joe4]

Welcome to the Board!

Add commands before your "Save" command that delete those two sheets.
If you don't know how to write VBA code to do that, just turn on your Macro Recorder and manually delete those two pages.
You will then have recorded VBA code that you need to do that. You can then copy that recorded code into your code above.

 

[excelnoobhere]

I would like to keep these sheets on my main workbook and not delete them, Is that possible?
the whole reason why I'm saving to another location is so that my main workbook does not get tempered with.
thank you,

 

[Joe4]

Note that the changes you are making (deleting the sheets) only apply to whatever workbook is saved AFTER you make that deletion.
So as long as you don't re-save your "main" workbook AFTER deleting the sheets, that copy will not be affected.

Perhaps you want to follow these steps in your VBA code:
1. Save your Main workbook
2. Delete the two sheets
3. Save to the other location
4. Close the file right away

By doing step 4, you will be closing the file after you delete the two sheets and saved to your other location. So there is no chance that you will accidentally then re-save over the original copy.

 

[Norie]

How about saving every worksheet except 'Main' and 'Template' to the new workbook?

 

[excelnoobhere]

How about saving every worksheet except 'Main' and 'Template' to the new workbook?

Thats more of what I'm looking for, is that possible?

 

[excelnoobhere]

Note that the changes you are making (deleting the sheets) only apply to whatever workbook is saved AFTER you make that deletion.
So as long as you don't re-save your "main" workbook AFTER deleting the sheets, that copy will not be affected.

Perhaps you want to follow these steps in your VBA code:
1. Save your Main workbook
2. Delete the two sheets
3. Save to the other location
4. Close the file right away

By doing step 4, you will be closing the file after you delete the two sheets and saved to your other location. So there is no chance that you will accidentally then re-save over the original copy.

that would work however, I'm not the only one using the main workbook and I'm afraid that someone will overwrite it with the deleted sheets

 

[Norie]

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

 

[Joe4]

that would work however, I'm not the only one using the main workbook and I'm afraid that someone will overwrite it with the deleted sheets

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).