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 ?

 

[Peter100]

Hi

Just tested this out and it only deletes one instance of TEMP, but not other sheets named TEMP01, 02 etc.

 

[jindon]

It is Case sensitive

TEMP*

 

[Peter100]

Hi Jindon

Sorry, that was just my typing the sheets are indeed Temp01 Temp02 etc

 

[jindon]

Can you show me your code?

 

[Blade Hunter]

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.

This is because you are testing and only moving ahead with the delete after you verified it starts with temp.

Remove the

END

from the code and it will fix your problem of only working on one sheet. Basically it currently quits after it deletes a sheet called temp.

Just a note on requesting help. It is a lot easier for us to code something up for you if you tell us your complete requirements from the start. It's more time consuming to keep adding things and making changes if the requirements keep changing.

Cheers

Dan

 

[Blade Hunter]

Try this

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

Edit: Yes I can confirm that it WILL delete hidden sheets too.

Cheers

Dan

 

[Peter100]

Hi Blade

That seems to Work brilliantly

Many thanks to you all

 

[Peter100]

Hi it's me again

Spent the last 2 hours testing it in real time and it works superbly. But as there are a lot of the TEMP files created, it is a bit annoying to have to keep clickng yes to delete the individual sheets.

Could it be made to only prompt once to remove all TEMP worksheets ?

 

[jindon]

try

Sub test()
Dim ws As Worksheet
If vbYes <> MsgBox("Delete all Temp sheet?", vbYesNo + vbQuestion) Then Exit Sub
On Error Resume Next
Application.DisplayAlerts = False
For Each ws In Sheets
    If LCase(ws.Name) Like "temp*" Then ws.Delete
Next
Application.DisplayAlerts = True
End Sub

 

[Peter100]

Hi Jindon

Would this go in the before close section ?