sub to delete all sheets other than x, y, z

[Max]

Looking for a sub which would delete all sheets
other than 3 sheets named as: x, y, z. Thanks.

 

[Gary Keramidas]

here's one way, give this a try:

Option Explicit

Sub remove_some_sheets()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "x" And ws.Name <> "y" And ws.Name <> "z" Then
ws.Delete
End If
Next
Application.DisplayAlerts = True
End Sub

 

[JLGWhiz]

Sub delShts()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "x" And sh.Name <> "y" And sh.Name <> "z" Then
sh.Delete
End If
Next
End Sub

 

[Max]

Works well, Gary. Thanks.

 

[Max]

Thanks, JLGWhiz
It works well too, after I inserted the lines:
Application.DisplayAlerts = False
....
Application.DisplayAlerts = True
(took the cue from Gary's sub)

 

[Chip Pearson]

Try something like the following. In this code, the sheet names must be in
UPPER CASE unless you have an "Option Compare Text" statement at the top of
the module (before and outside of any procedures), in which case the case of
the name doesn't matter.

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
Select Case UCase(WS.Name)
Case "X", "Y", "Z"
' do nothing
Case Else
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
End Select
Next WS


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Max]

Thanks, Chip ! That works well, too.