Delete a sheet if it has a specific name

[thebigtable]

I would like to delete the sheets
TEST
BOARD
CHECK
from a workbook. They may contain all 3 or may only have 1 or 2.
I need to confirm the delete automatically.

thanks,
J@thebigtable

 

[Banker1]

Try this:

Sub DeleteSheet()
Dim WS As Worksheet

Application.DisplayAlerts = False

For Each WS In ActiveWorkbook.Worksheets
    If UCase(WS.Name) = "TEST" Then
        WS.Delete
    ElseIf UCase(WS.Name) = "BOARD" Then
        WS.Delete
    ElseIf UCase(WS.Name) = "CHECK" Then
        WS.Delete
    End If
Next WS

Application.DisplayAlerts = True

End Sub

I set the code up in my personal workbook so I have access to it all the time. If you need it only in one workbook (by the sound of it you need it all the time), paste the code into that workbook.

This would work as well:

Sub DeleteSheet()
Dim WS As Worksheet

Application.DisplayAlerts = False


For Each WS In ActiveWorkbook.Worksheets
    If UCase(WS.Name) = "TEST" Or UCase(WS.Name) = "BOARD" Or UCase(WS.Name) = "CHECK" Then
        WS.Delete
    End If
Next WS

Application.DisplayAlerts = True

End Sub

 

[lenze]

May something like this?

Sub DeleteSheet()
Dim sh As Worksheet
On Error GoTo Trap:
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Sheets
 Select Case sh.Name
  Case "TEST", "BOARD", "CHECK": sh.Delete
  Case Else:
End Select
Next sh
Trap:
Application.DisplayAlerts = True
Exit Sub
End Sub

lenze

 

[thebigtable]

Thanks Banker 1. That was perfect.

kindest regards,
J@thebigtable

 

[thebigtable]

What if they may not be Upper case?

J

 

[Jonmo1]

Why bother testing the sheet name?
Just delete the sheet and ignore the error when it doesn't exist...

Application.DisplayAlerts = False
On Error Resume Next
Sheets("TEST").Delete
Sheets("BOARD").Delete
Sheets("Check").Delete
On Error Goto 0
Application.DisplayAlerts = True

 

[thebigtable]

many thanks

j@thebigtable

 

[Banker1]

What if they may not be Upper case?

J

The UCase(WS.Name) = "XXXX" converts all the tab names to upper case, entering "XXXX" in all uppercase will make the structures the same. VBA is particular about string case, I have found forcing everything to uppercase is the only way to ensure you accomplish what you set out to do.

 

[Tom Urtis]

The UCase(WS.Name) = "XXXX" converts all the tab names to upper case, entering "XXXX" in all uppercase will make the structures the same. VBA is particular about string case, I have found forcing everything to uppercase is the only way to ensure you accomplish what you set out to do.

I prefer not using UCASE, and instead, above and outside all macros at the top of the module, maintain the statement
Option Compare Text
and you won't have to worry about remembering UCASE at every codeline where you otherwise would have.