Deleting mutliple worksheets from a workbook if they conatin a specific tab name.

[ruff]

HI,

I have created the below macro to do this but it does not run through the whole workbook, I keep having to re-run the macro to delete other sheets (Some workbooks conatin 6-7 worksheets that end in Cube or Chart) Also If I could get it to stop asking me before deleting the worksheets and just delete them that would be great.


Sub delete_Cube_Chart_Tree()
For Each SheetExists In Worksheets
If Right(SheetExists.Name, 4) = "Cube" Then
SheetExists.Delete
Application.DisplayAlerts = True
Exit For
End If
Next SheetExists

For Each SheetExists In Worksheets
If Right(SheetExists.Name, 5) = "Chart" Then
SheetExists.Delete
Application.DisplayAlerts = True
Exit For
End If
Next SheetExists

For Each SheetExists In Worksheets
If Right(SheetExists.Name, 4) = "Tree" Then
SheetExists.Delete
Application.DisplayAlerts = True
Exit For
End If
Next SheetExists


End Sub

 

[wigi]

Why do you use "Exit For", given that your loop stops too early?

Can you please use

 tags here? Thanks.

 

[nuked]

 Application.DisplayAlerts = False

...prior to deleting the sheet would prevent the warning. Interestingly, you already have code that reverts it to True.

 

[Andrew Poulsom]

Does this work for you?

Sub delete_Cube_Chart_Tree()
    Dim i As Long
    Application.DisplayAlerts = False
    For i = Worksheets.Count To 1 Step -1
        If Right(Worksheets(i).Name, 4) = "Cube" Or Right(Worksheets(i).Name, 5) = "Chart" Or Right(Worksheets(i).Name, 4) = "Tree" Then
            Worksheets(i).Delete
        End If
    Next i
    Application.DisplayAlerts = True
End Sub

You can't use For Each ... Next when deleting.

 

[wigi]

You can't use For Each ... Next when deleting.[/QUOTE]

Hi Andrew

I set up an example workbook regarding this topic, and this code just works as expected:

[code]Sub delete_Cube_Chart_Tree()
    Application.DisplayAlerts = False
        For Each SheetExists In Worksheets
            If Right(SheetExists.Name, 5) = "chart" Then
                SheetExists.Delete
            End If
        Next
    Application.DisplayAlerts = True
End Sub

Can you maybe qualify your statement?

 

[Andrew Poulsom]

I was thinking it would behave like For Each Cell in Range, but apparently not.