Delete all sheets in workbook that contain "Dump" in the name

[Porr via OfficeKB.com]

Hello,

I have a workbook that generally contains 15-18 worksheets. Is it possible
to automate the deletion of only the worksheets with a name that contains the
word "Dump". The amount of worksheets that contain the word "Dump" ranges
from 3-7.

Thanks,

Patrick

 

[Dave Peterson]

You could use a macro:

Option Explicit
Sub testme()

Dim sh As Object 'could be any kind of sheet
Dim HowManyDeleted As Long

HowManyDeleted = 0
For Each sh In ActiveWorkbook.Sheets
If LCase(sh.Name) Like LCase("*dump*") Then
Application.DisplayAlerts = False 'no "Are you sure" prompt
On Error Resume Next
sh.Delete
If Err.Number <> 0 Then
'it failed
Err.Clear
MsgBox "Sheet: " & sh.Name & " was not deleted!"
Else
HowManyDeleted = HowManyDeleted + 1
End If
On Error GoTo 0
Application.DisplayAlerts = True
End If
Next sh

MsgBox "Deleted: " & HowManyDeleted & " sheet(s)."

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Porr via OfficeKB.com]

Worked great, I apprecaite it!

You could use a macro:

Option Explicit
Sub testme()

Dim sh As Object 'could be any kind of sheet
Dim HowManyDeleted As Long

HowManyDeleted = 0
For Each sh In ActiveWorkbook.Sheets
If LCase(sh.Name) Like LCase("*dump*") Then
Application.DisplayAlerts = False 'no "Are you sure" prompt
On Error Resume Next
sh.Delete
If Err.Number <> 0 Then
'it failed
Err.Clear
MsgBox "Sheet: " & sh.Name & " was not deleted!"
Else
HowManyDeleted = HowManyDeleted + 1
End If
On Error GoTo 0
Application.DisplayAlerts = True
End If
Next sh

MsgBox "Deleted: " & HowManyDeleted & " sheet(s)."

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Hello,

[quoted text clipped - 10 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201005/1