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

 

[Joe4]

Try this:

Sub deleteSheets()

    Dim sh2 As Worksheet, c As Range

    Set sh2 = Sheets("Points")

    Application.DisplayAlerts = False
    On Error Resume Next
    For Each c In sh2.Range("B5", sh2.Cells(Rows.Count, 2).End(xlUp))
        Sheets(c.Value).Delete
    Next c
    On Error GoTo 0
    
    Application.DisplayAlerts = True

End Sub

 

[My Aswer Is This]

I think this last display alerts should be set to true. is that not correct?

Application.DisplayAlerts = False

​

 

[Joe4]

I think this last display alerts should be set to true. is that not correct?

Application.DisplayAlerts = False

It is.
(I fixed it just before you posted that).

 

[aerobharath15]

Many thanks Joe.

Sorry. I made a slight change to worksheet. B5 cell must be replaced with B15. I changed it a bit and when i run it only starts to delete the worksheets one by one from B17 and not from B15 where the first worksheet on the list begins.

Also, is it not possible to delete all the worksheets from the list at once instead of one by one?

Many thanks again

 

[aerobharath15]

this is the updated code.

Sub GENERATE()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("PCM Template")
Set sh2 = Sheets("PCM SUMMARY")
For Each c In sh2.Range("B15", sh2.Cells(Rows.Count, 2).End(xlUp))
sh1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value: ActiveSheet.Range("B28") = c.Value
Next
End Sub

 

[Fluff]

How about

Sub DelShts()
   Dim Ary As Variant
   Dim i As Long
   Dim Cl As Range
   
   ReDim Ary(1 To Worksheets.Count)
   With Sheets("PCM Summary")
      For Each Cl In .Range("B15", .Range("B" & Rows.Count).End(xlUp))
         If Evaluate("isref('" & Cl.Value & "'!a1)") Then
            i = i + 1
            Ary(i) = Cl.Value
         End If
      Next Cl
   End With
   ReDim Preserve Ary(1 To i)
   Application.DisplayAlerts = False
   Sheets(Ary).Delete
   Application.DisplayAlerts = True
End Sub

 

[aerobharath15]

How about

Sub DelShts()
   Dim Ary As Variant
   Dim i As Long
   Dim Cl As Range
   
   ReDim Ary(1 To Worksheets.Count)
   With Sheets("PCM Summary")
      For Each Cl In .Range("B15", .Range("B" & Rows.Count).End(xlUp))
         If Evaluate("isref('" & Cl.Value & "'!a1)") Then
            i = i + 1
            Ary(i) = Cl.Value
         End If
      Next Cl
   End With
   ReDim Preserve Ary(1 To i)
   Application.DisplayAlerts = False
   Sheets(Ary).Delete
   Application.DisplayAlerts = True
End Sub

hi, thanks but it shows an error SUBSCRIPT OUT OF RANGE

 

[Fluff]

Which line is highlighted?

 

[Joe4]

Also, is it not possible to delete all the worksheets from the list at once instead of one by one?

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.