Deleting Sheets

  • Thread starter Thread starter Jordan
  • Start date Start date
J

Jordan

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?
 
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
 
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,

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
 
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)
 
Back
Top