Deleting Sheets WITHOUT having to click confirm...

[GS7CLW]

Using this code to delete sheets I no longer want to include in my wb; but, I still have to click the confirm msg box that pops up for every sheet...

Sub DeleteOldSheets()
Dim Worksheet
Dim RSID As String
Dim WS_Count As Integer
   Dim I As Integer
   ' Set WS_Count equal to the number of worksheets in the active
   ' workbook.
   WS_Count = ActiveWorkbook.Worksheets.Count
   ' Begin the loop.
   For I = 1 To WS_Count
    ActiveWorkbook.Worksheets(I).Select
    RSID = ActiveSheet.Name
    Sheets(RSID).Select
    If Len(RSID) < 5 Then
    ActiveWindow.SelectedSheets.Delete
    End If
   Next I
End Sub

any way around it??

hope all are having a great one -- HUMP DAY!!!!
cliff

 

[Oaktree]

Try turning off DisplayAlerts:

Application.DisplayAlerts = False
Sheets("sheet name goes here").Delete
Application.DisplayAlerts = True

 

[Yahya]

Just add this two lines

Sub DeleteOldSheets()
Dim Worksheet
Dim RSID As String
Dim WS_Count As Integer
   Dim I As Integer
   ' Set WS_Count equal to the number of worksheets in the active
   ' workbook.
[B][U]Application.DisplayAlerts = False[/U][/B]
  WS_Count = ActiveWorkbook.Worksheets.Count
   ' Begin the loop.
   For I = 1 To WS_Count
    ActiveWorkbook.Worksheets(I).Select
    RSID = ActiveSheet.Name
    Sheets(RSID).Select
    If Len(RSID) < 5 Then
    ActiveWindow.SelectedSheets.Delete
    End If
   Next I
[B][U]Application.DisplayAlerts = True[/U][/B]

End Sub

 

[GS7CLW]

works perfectly.

And after I got past that issue another "popped up"...

It stops and highlights:
ActiveWorkbook.Worksheets(I).Select

which I am sure is because the I (WS_Count) changes after each sheet is deleted. So, I believe I need to increment I backwards -- correct?

 

[wigi]

For I = WS_Count To 1 Step -1

 

[GS7CLW]

Absolutely!!!!

I really should have remembered that but it has been over a decade since I used VB\A.

THANK YOU!!!!!!!!!!!!!!

cliff

 

[Ruddles]

Personally I always cycle through the WorkSheets collection and then I don't need to worry about the order of the worksheets changing mid-processing.

Your code appears to be selecting each sheet by its number, storing its name, then selecting it again by its name and deleting it if its name is less than five characters in length. Have I got it right?

If so, I think you just need this:-

  Dim ws As WorkSheet
 
  For Each ws In ThisWorkbook.WorkSheets
    If Len(ws.Name) < 5 Then
      Application.DisplayAlerts = False
      ws.Delete
      Application.DisplayAlerts = True
    End If
  Next ws

 

[GS7CLW]

YEP 'ER!!
tested and works great
Thank YOU for your help.
Have a great week\end!
cliff