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

ruff

New Member
Joined
Jan 19, 2012
Messages
10
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Why do you use "Exit For", given that your loop stops too early?

Can you please use
Code:
 tags here? Thanks.
 
Upvote 0
Code:
 Application.DisplayAlerts = False
...prior to deleting the sheet would prevent the warning. Interestingly, you already have code that reverts it to True.
 
Upvote 0
Does this work for you?

Code:
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.
 
Upvote 0
Code:
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?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top