delete all sheets except...

[caroline]

Hi,
I am trying to delete all sheets except 3 (one of
which "admin" has got the names of the 2 other not to
delete in cells that are named "name2" and "name2").
Here's my code but it is falling over. Any idea?
Thanks

Call UnprotectWorkbook

Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
If LCase(sh.Name) <> "admin" And LCase(sh.Name)
<> Range("name1").Value And LCase(sh.Name) <> Range
("name2").Value Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

 

[Bob Phillips]

Caroline,

I have changed the code a bit to ensure no wrap-around

Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
If (LCase(sh.Name) <> "admin" And _
LCase(sh.Name) <> Range("name1").Value And _
LCase(sh.Name) <> Range("name2").Value) Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

YHou also would probably be best to qualify the range names by the worksheet
in case that is not active

Worksheets("Sheet1").Range("name1").Value

--

HTH

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

 

[caroline]

Thanks a lot, Bob. It works.

-----Original Message-----
Caroline,

I have changed the code a bit to ensure no wrap-around

Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
If (LCase(sh.Name) <> "admin" And _
LCase(sh.Name) <> Range("name1").Value And _
LCase(sh.Name) <> Range("name2").Value) Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

YHou also would probably be best to qualify the range names by the worksheet
in case that is not active

Worksheets("Sheet1").Range("name1").Value

--

HTH

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




.

 

[Dana DeLouis]

Just another general idea.

Nme1 = UCase([name1])
Nme2 = UCase([name2])
For Each sh In ThisWorkbook.Worksheets
Select Case UCase(sh.Name)
Case "ADMIN", Nme1, Nme2
'Do Nothing
Case Else
sh.Delete
End Select
Next sh

 

[Tushar Mehta]

A nitpicking detail. Your code assumes that the activesheet is the
ADMIN sheet. It also assumes that the names in the cells are in lower
case. If either of these conditions is not true (and Option Compare
Binary is set), the result could be disastrous.

An untested alternative:

Option Explicit

Sub testIt()
Dim Protect1 As String, Protect2 As String, WS As Worksheet
With Worksheets("admin")
Protect1 = UCase(.Range("name1").Value)
Protect2 = UCase(.Range("name2").Value)
End With
For Each WS In ThisWorkbook.Worksheets
Select Case UCase(WS.Name)
Case "ADMIN", Protect1, Protect2
'Do Nothing
Case Else
WS.Delete
End Select
Next WS
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions