Save As entire workbook except certain sheets

excelnoobhere

Board Regular
Joined
Mar 11, 2019
Messages
61
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You can delete the 2 worksheets before saving.
Code:
    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
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
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.
 
Upvote 0
How about saving every worksheet except 'Main' and 'Template' to the new workbook?
 
Upvote 0
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
 
Upvote 0
Sure, give this a try.

Code:
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
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top