If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Delete all sheets in workbook that contain "Dump" in the name
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201005/1 |
#2
|
|||
|
|||
Delete all sheets in workbook that contain "Dump" in the name
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 he 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" wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201005/1 -- Dave Peterson |
#3
|
|||
|
|||
Delete all sheets in workbook that contain "Dump" in the name
Worked great, I apprecaite it!
Dave Peterson wrote: 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 he 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.a...tions/201005/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201005/1 |
Thread Tools | |
Display Modes | |
|
|