Deleting Worksheets in a table

Hi everyone,

I've been trying to ammend code that is on the net but to no avail... Anyone know the VBA to delete sheets that are contained within a range at all?

In the workbook I am creating I have a macro that pulls redundant data and puts it into a separate table however from there, I cannot work out how to get the VBA to cycle through and delete only the worksheets that are in this table.

I can get it to delete everything and then the first item but not do what I want.

I have attached a sample work book for you to see if you can help. It has four worksheets each one a colour and a further sheet marked as data to delete which has three of the items on it. Can anyone see how I could run a loop to only delete the sheets in this range?

Help as ever is appreciated

Regards

R

Sub deleteme()
    Dim sh As Worksheet
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0
    For Each sh In Worksheets
        If sh.Name <> "DATA TO DELETE" And sh.Name <> "PURPLE" Then sh.Delete
    Next sh
      Application.ScreenUpdating = 1
     Application.DisplayAlerts = 1
End Sub

Thank you, for the reply however when I ran this against my sample file it worked but the main data that I am using differs massively from the sample (not about colours at all) so this code won't fit.

I understand that you have added <> "Data to Delete" so that it doesn't delete the worksheet that the data is contained in but in my live workbook there are about 70 worksheets I need the Macro to delete only items contained within the defined table.

I tried modifying your code to achieve this but I got back to the same issue that it deleted everything.

Really appreciate your help though, any further ideas?

Regards

R

Okay! which sheets do you want to delete? Is there a pattern to the name of your sheeets? Or on which criteria do you delete any particular sheet? You need to give me more information

Hey Ab33 thanks for replying and offering help! I'll try to be clearer...

In this example the worksheet contains a table in which the data that I want to delete resides. In my live environment this will change on a daily basis.
The worksheets in the live book are made up of these unique references some of which are live and some of which I want to delete.

In this example book that I have attached I would want the VBA to look through the list of all items in the table (this may contain one item or hundreds) and delete the worksheet that is defined on each line of this table but leave all other worksheets intact.

I hope that this helps explain what I am trying to achieve?

Okay,
You look on column A list which has a drop down list, so if you pick up the name of sheet from list A, it has to be deleted. Is this right?

That's it anything that appears in the list in Column A needs to be deleted in the example that's red, green, blue so I need the code to delete the worksheets red, green, blue. If for example only red was in the list I would want it to only delete the red worksheet.
I know that I know the answer to this but can't get there.

Thanks again

Sub deleteme()
    Dim sh As Worksheet
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0
    For Each sh In Worksheets
    On Error Resume Next
        LR = Sheets("DATA TO DELETE").Cells(Rows.Count, 1).End(xlUp).Row
        If sh.Name <> "DATA TO DELETE" Then
            For i = 2 To LR
             If Cells(i, 1) = sh.Name Then sh.Delete
            Next i
        End If
    Next sh
      Application.ScreenUpdating = 1
     Application.DisplayAlerts = 1
End Sub

The solution worked and helped me achieve what I was finally looking for with my main project,


Sub DeleteSheets()

Dim wks As Worksheet
    Dim MyRange As Range
    Dim Cell As Range
    
    Set wks = Worksheets("BREAKS CLEARED")
    
    With wks
        Set MyRange = Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
    End With
    
    On Error Resume Next
    Application.DisplayAlerts = False
    For Each Cell In MyRange
        Sheets(Cell.Value).Delete
    Next Cell
    Application.DisplayAlerts = True
    On Error GoTo 0
    
End Sub