Automatically Delete Certain Sheets Upon Save As

RickWHaas · Sep 14th 2009

Our company has designed a Excel Document for quoting purposes. The problem we are having is that certain sheets that are needed to do the quote do not need to be used after the quote is finished and saved. These extra worksheets are taking up a lot of room on our server.

In the document I have already disabled the save feature so that the saveas has to be used. Is there code that can delete sheets 3, 5, 6, 7, 8 upon doing a save as?

Thanks

AAE · Sep 14th 2009

Re: Automatically Delete Certain Sheets Upon Saveas

In the workbook module:

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


    Application.DisplayAlerts = False
    
    With ThisWorkbook
        On Error Resume Next
        Sheets(Array("sheet3", "sheet5", "sheet6", "sheet7", "Sheet8")).Delete
        On Error GoTo 0
    End With
    
    Application.DisplayAlerts = True


End Sub

Display More

ADDED BY ADMIN;

If ONLY on Save As, you need an IF Statement;

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     
    If SaveAsUI = True Then
        Application.DisplayAlerts = False
         
        With ThisWorkbook
            On Error Resume Next
             Sheets(Array("sheet3", "sheet5", "sheet6", "sheet7", "Sheet8")).Delete
            On Error GoTo 0
        End With
         
        Application.DisplayAlerts = True
    End If
     
End Sub

Display More

RickWHaas · Sep 14th 2009

Re: Automatically Delete Certain Sheets Upon Saveas

This worked perfect. The only thing I had to change was the tab reference. The tabs actually have different names other than 1, 2, 3... so I reference just the sheets(1), sheets(2)... I also added two lines to make it go back to the first tab instead of the last which was getting really annoying. The code is below.

Again, you guys are great

Thanks for your help.

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


     Application.DisplayAlerts = False
     
    With ThisWorkbook
        On Error Resume Next
         Sheets(Array(3, 5, 6, 7, 8, 9)).Delete
        On Error GoTo 0
        Sheets(1).Select
        Range("B9").Select
    End With
     
    Application.DisplayAlerts = True
     
End Sub

Display More

AAE · Sep 14th 2009

Re: Automatically Delete Certain Sheets Upon Saveas

You can replace the two line of codes that select the sheet/cell with this:

Code

Application.Goto reference:=Sheets(1).Range("B9")

Dave Hawley · Sep 15th 2009

Re: Automatically Delete Certain Sheets Upon Saveas

If ONLY on Save As, you need an IF Statement;

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     
    If SaveAsUI = True Then
        Application.DisplayAlerts = False
         
        With ThisWorkbook
            On Error Resume Next
             Sheets(Array("sheet3", "sheet5", "sheet6", "sheet7", "Sheet8")).Delete
            On Error GoTo 0
        End With
         
        Application.DisplayAlerts = True
    End If
     
End Sub

Display More

Automatically Delete Certain Sheets Upon Save As

Participate now!

Share