Delete worksheets based on a list

[aerobharath15]

hi All,

With the help of a member here, i managed to create multiple WORKSHEETS based on a list using the code below. but now i want a macro to delete all the created worksheets without deletion prompt window. Please someone can help urgently.

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Points")
For Each c In sh2.Range("B5", sh2.Cells(Rows.Count, 2).End(xlUp))
sh1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value: ActiveSheet.Range("D5") = c.Value
Next
End Sub

 

[aerobharath15]

If it is all being done automatically by a macro, what difference does it really make?

Are there any other sheets other than "Points" and "Template" that you want to keep?
If not, you can just delete all sheets not named those, and there is no need to loop through a list somewhere on your sheet.

Hi Joe, thats a good point. We can delete all sheets except the 2 sheets above.

Also, please can you provide the fix i requested? Your code is deleting the sheets from the list from third row. first and second row from the list is not touched at all. thanks.

 

[aerobharath15]

Which line is highlighted?

it doesn't highlight any line

 

[Joe4]

Hi Joe, thats a good point. We can delete all sheets except the 2 sheets above.

Then try this code. You don't need to worry about the list in this case.

Sub DelSheets()

    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each ws In Worksheets
        If ws.Name <> "Points" And ws.Name <> "Template" Then ws.Delete
    Next ws
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

 

[aerobharath15]

Then try this code. You don't need to worry about the list in this case.

Sub DelSheets()

    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each ws In Worksheets
        If ws.Name <> "Points" And ws.Name <> "Template" Then ws.Delete
    Next ws
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

Works perffectly.. thanks a lot

 

[Joe4]

You are welcome.