Why my Sub not work?

[Amolin]

Sub deleteSheet()

Worksheets.Add

S = Chr(34)

For m = 1 To Worksheets.Count
If Left(Worksheets(m).Name, 5) <> "Sheet" Then
SelectedSheets = Chr(34) & Worksheets(m).Name & Chr(34)
Exit For
End If
Next


For n = m + 1 To Worksheets.Count
If Left(Worksheets(n).Name, 5) <> "Sheet" Then
SelectedSheets = SelectedSheets & "," & Chr(34) & Worksheets(n).Name
Chr(34)
End If
Next


S = Val(SelectedSheets)

Worksheets(Array(SelectedSheets)).Delete 'REPORT ERROR!!!!

Stop

End Su

 

[Frank Kabel]

Hi
not really sure what you're trying to do but maybe the following is
what you're looking for (deleting all sheets which do not beging with
'Sheet'):

sub delete_sheets
Dim i
for i = activeworkbook.worksheets.count to 1 step -1
if lcase(left(worksheets(i).name,5))<>"sheet" then
application.displayalerts=false
worksheets(i).delete
application.displayalerts=true
end if
next
end sub

 

[David McRitchie]

Certainly what it looks like, but sure don't delete my renamed
sheets (nor sheets with macros) as quickly as I delete sheets
that still have sheetnames beginning with "sheet" AND don't
have macros with them. If I took the effort to give them a name
I'm certainly not as inclined to delete them. Nor do I automatically
delete them -- I at least look at it.

Delete Active Sheet if name begins with "sheet" (#DeleteThisSheet)
and does not have macros.
Worksheets in VBA Coding and in Worksheet Formulas
http://www.mvps.org/dmcritchie/excel/sheets.htm#deletethissheet