VBA Delete if sheet exists

[Peter100]

Hi
Before closing a workbook I want to check if a sheet called temp exists. If it does then I want to delete it.

Can you help ?

 

[Blade Hunter]

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name = "Temp" Then
        Application.DisplayAlerts = False
        Sheets("Temp").Delete
        Application.DisplayAlerts = True
        End
    End If
Next
End Sub

Paste this at the workbook level in the VBE

 

[Peter100]

Cheers Blade that worked great.

Could one build in a essage box to say delete or not then take the appropriate action ?

 

[Peter100]

Sorry
forgive the typo, that should be Message box

 

[Blade Hunter]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Dim FoundTemp As Boolean
For Each ws In Worksheets
    If ws.Name = "Temp" Then
        If MsgBox("Would you like to delete the Temp sheet?", vbYesNo, "Delete Temp?") = vbYes Then
            Application.DisplayAlerts = False
            Sheets("Temp").Delete
            Application.DisplayAlerts = True
            End
        End If
    End If
Next
End Sub

 

[Peter100]

Blade Hunter

Once again THANK YOU

 

[Blade Hunter]

Anytime, happy to help

 

[Peter100]

Hi Again

Turning it into a realtime situation, I indeed have several sheets called Temp, ie Temp01, Temp02, Temp03 etc.

So I changed the
If ws.Name = "Temp" Then

To

If ws.Name Like "Temp*" Then

Which worked great, but it errored when I tried to modify the
Sheets("Temp").Delete

What is the answer ?

 

[Fazza]

Maybe use ws.delete

BTW, consider checking before deleting that you are not about to delete the sole worksheet in a workbook.

 

[Peter100]

Cheers Fazza
I thought that would delete all sheets but it worked a treat as long as the sheets weren't hidden as these are. Is there a way to delete them even if hidden ?

Forgive me keep coming back but I thought I'd keep the main question brief nut hadn't realised the other problems.