Deleting Sheets
 

Lets say i have a workbook with 20 sheets on it though the
number may vary and I want to delete all sheets which
arent named "Control", "Menu" and "Table"

How could i accomplish this with VBA?

Deleting Sheets
 

Jordan,

Try something like the following:

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
Select Case WS.Name
Case "Control", "Menu", "Table"
Case Else
WS.Delete
End Select
Next WS

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Jordan" wrote in message
...
Lets say i have a workbook with 20 sheets on it though the
number may vary and I want to delete all sheets which
arent named "Control", "Menu" and "Table"

How could i accomplish this with VBA?

Deleting Sheets
 

Hi Jordan
similar to a post 2 hours ago try
----
Public Sub delete_sheets()
Dim wkSht As Worksheet
Application.DisplayAlerts = False
For Each wkSht In Worksheets
If Lcase(trim(wkSht.Name)) < "Control" and
Lcase(trim(wkSht.Name)) _
< "Menu" Lcase(trim(wkSht.Name)) < "Table" Then
wkSht.Delete
End If
Next wkSht
Application.DisplayAlerts = True
End Sub

Frank

Jordan wrote:
Lets say i have a workbook with 20 sheets on it though the
number may vary and I want to delete all sheets which
arent named "Control", "Menu" and "Table"

How could i accomplish this with VBA?

Deleting Sheets
 

Jordan,

A better version is below:

Dim WS As Worksheet
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets
Select Case WS.Name
Case "Control", "Menu", "Table"
Case Else
WS.Delete
End Select
If ThisWorkbook.Worksheets.Count = 1 Then
Exit For
End If
Next WS
Application.DisplayAlerts = True

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jordan" wrote in message
...
Lets say i have a workbook with 20 sheets on it though the
number may vary and I want to delete all sheets which
arent named "Control", "Menu" and "Table"

How could i accomplish this with VBA?

Deleting Sheets
 

Hi Jordan,

How was the jungle?

Application.DisplayAlerts = False
For Each sh In Activeworkbook.Worksheets
If sh.Name < "Control" And sh.Name < "Menu" And _
sh.Name < "Table" Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jordan" wrote in message
...
Lets say i have a workbook with 20 sheets on it though the
number may vary and I want to delete all sheets which
arent named "Control", "Menu" and "Table"

How could i accomplish this with VBA?