I would like to write a macro that deletes all worksheets in a workbook
except for two, called "Buttons" & "Data". The problem is that the amount of
and names of the worksheets won't be static, so I can't simply write
"Worksheets ("Sheet1").Delete". I thank you in advance for your help.

Sub TryNow()
Dim mySht As Worksheet
Application.DisplayAlerts = False
For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name < "Buttons" And mySht.Name < "Data" Then
mySht.Delete
End If
Next mySht
Application.DisplayAlerts = True
End Sub

HTH,
Bernie
MS Excel MVP


"lewscannon" wrote in message
...
I would like to write a macro that deletes all worksheets in a workbook
except for two, called "Buttons" & "Data". The problem is that the amount
of
and names of the worksheets won't be static, so I can't simply write
"Worksheets ("Sheet1").Delete". I thank you in advance for your help.

Hi,

Try this:

Sub Test()
Set wb=ThisWorkbook
Application.DisplayAlerts=False
For i=wb.Sheets.Count to 1 Step -1
Select case wb.Sheets(i).Name
Case "Buttons", "Data"
Case Else: wb.Sheets(i).Delete
End Select
Next i
Application.DisplayAlerts=True
End Sub

Regards,
KL


"lewscannon" wrote in message
...
I would like to write a macro that deletes all worksheets in a workbook
except for two, called "Buttons" & "Data". The problem is that the amount
of
and names of the worksheets won't be static, so I can't simply write
"Worksheets ("Sheet1").Delete". I thank you in advance for your help.

lewscannon, here is one way,

Sub delete_sheets()
'deletes all sheets except Buttons and Data
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name < "Buttons" And ws.Name < "Data" Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = ture
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"lewscannon" wrote in message
...
I would like to write a macro that deletes all worksheets in a workbook
except for two, called "Buttons" & "Data". The problem is that the amount
of
and names of the worksheets won't be static, so I can't simply write
"Worksheets ("Sheet1").Delete". I thank you in advance for your help.

Thank you all for your help.

"lewscannon" wrote:

I would like to write a macro that deletes all worksheets in a workbook
except for two, called "Buttons" & "Data". The problem is that the amount of
and names of the worksheets won't be static, so I can't simply write
"Worksheets ("Sheet1").Delete". I thank you in advance for your help.