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 ?

 

[jindon]

Yes...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
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

 

[Blade Hunter]

If you still want it to only prompt when it finds temp sheets, this will do it, otherwise Jindons code will do what you need.

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

 

[Peter100]

Hi Gentlemen

Both work well within a workbook, but I can't make it work within the addin.

Blade Hunter

Your previous code however did

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

 

[Peter100]

Hi

I think I'm wrong in saying your previous code worked as was. As you are aware this code has been developing during this thread and I have been modifying it as it went and I might have got some residue left over that makes it work. the code that works fine is

Private Sub AllBooks_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)
Dim ws As Worksheet
Dim FoundDPFB As Boolean

If (Wb.Name Like "*.XLA" Or Wb.Name Like "*.xla") Then

Rem do nothing
Else


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 If
End Sub

Any comments ?

 

[Peter100]

Amendement

should have read

Dim FoundTEMP As Boolean

 

[Peter100]

The code I just posted Was Mikericksons sorry for the error

 

[robmaag]

Blade, I am trying to delete a worksheet from a series of excel workbooks and I am getting a type mismatch error:

Sub Delete_Tracker()
Dim ws As Worksheet
Dim Fso, fldr
Dim wsk As Workbooks
Set Fso = CreateObject("scripting.filesystemobject")
Set fldr = Fso.GetFolder("C:\Report")
'Dim FoundTemp As Boolean
For Each wsk In Application.Workbooks
If ws.Name = "0100_Member_Tracker" Then
If MsgBox("Would you like to delete the 0100_Member_Tracker sheet?", vbYesNo, "Delete Temp?") = vbYes Then
Application.DisplayAlerts = False
Sheets("0100_Member_Tracker").Delete
Application.DisplayAlerts = True
End
End If
End If
Next
End Sub

 

[robmaag]

Hi, to whom it may concern:

I am really stuck...I just can't figure out why I can't delete the following worksheets...Please help...

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wks As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\Report"
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
.Filename = "*.xlsx"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

'DO YOUR CODE HERE
For Each wks In Worksheets
If wks.Name = "HCC Values" Then wks.Delete
If wks.Name = "0100_Member_Tracker" Then wks.Delete
If wks.Name = "Home" Then wks.Delete
Next wks

wbResults.Close SaveChanges:=False
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox "Done with looping"
End Sub

 

[scrybbler]

FYI, if you're like me, sucking up old answers, you might hit a small snag with this snippet. See below. Still, big props to BH and the others for this great info!

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 <<< fyi I'm pretty sure this is an error. this dumps out of the procedure.
    End If
Next
End Sub

Paste this at the workbook level in the VBE

 

[johnnyb5]

Thanks Blade, I just found your solution and modified it to work for my own temp sheet deletion needs. You solved my problem before I knew it existed.
Much Thanks Again,
JB