Delete on Workbook Close

[helpexcel]

I'm trying to clear the worksheet and delete any added worksheets when the workbook is closed. this is the code I put in ThisWorkbook, but it doesn't work.

Private Sub Workbook_Deactivate()

    Set ws9 = Sheet9
    ws9.Cells.Delete
    
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Main Sheet" Then ws.Delete
Next
    
    
    
End Sub

 

[earthworm]

Where does the error appears ?

 

[helpexcel]

it doesn't error, it just doesn't do anything.

 

[Fluff]

If you want it to work when you close the workbook, you will need to use the BeforeClose event, rather than the deactivate event. However you will also need to save the workbook, otherwise you will loose the changes.

 

[helpexcel]

Thanks, I updated to BeforeClose and it works like a charm!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[helpexcel]

I tried to add two sheets to the code, and now it deletes all of the sheets but the last one added.

Dim WS As Worksheet
For Each WS In Worksheets
If WS.Name <> "Main Sheet" Or "Total" Or "Graph" Then WS.Delete
Next

 

[Fluff]

Are you trying to keep sheets Main, Total & Graph and delete all the others?

 

[helpexcel]

yes.

 

[Fluff]

Ok, how about

Dim WS As Worksheet
For Each WS In Worksheets
   Select Case WS.Name
      Case "Main Sheet", "Total", "Graph"
      Case Else
         WS.Delete
   End Select
Next