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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,821
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,821
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
How about saving every worksheet except 'Main' and 'Template' to the new workbook?
 

excelnoobhere

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,821
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,112,816
Messages
5,542,660
Members
410,567
Latest member
SCraig123
Top