Here's a generic proc you can use to delete all sheets except one or
more specified sheets. The SheetsToKeep variant can be a scalar
sheet identifier (either a String sheet name or a Integer position) or
an array of sheet identifiers (each of which can be a String name or
Integer position). WhichWorkbook is optional and if present specifies
which workbook in which to get the sheets.

The sheet numbers passed in via SheetsToKeep are their positions
before the code runs. Therefore, they do not change as sheets are
deleted. In this sense, they are absolute sheet positions. If during
the Delete loop you get down to a single visible worksheet, the code
exits. You can then call this and pass it any sort of array you want.

E.,g\

Dim Arr() As Variant
ReDim Arr(1 To 3)
Arr(1) = "Sheet1"
Arr(2) = 3
Arr(3) = "does not exist"
DeleteSheets SheetsToKeep:=Arr


' OR
Dim S As String
S = "sheet10;sheet8"
DeleteSheets SheetsToKeep:=Split(S, ";")

or any number of other ways to create the input array.


Sub DeleteSheets(SheetsToKeep As Variant, _
Optional WhichWorkbook As Workbook)
Dim WB As Workbook
Dim Arr() As String
Dim N As Long
Dim V As Variant

If WhichWorkbook Is Nothing Then
Set WB = ThisWorkbook
Else
Set WB = WhichWorkbook
End If

If IsArray(SheetsToKeep) Then
ReDim Arr(LBound(SheetsToKeep) To UBound(SheetsToKeep))
On Error Resume Next
For N = LBound(SheetsToKeep) To UBound(SheetsToKeep)
Arr(N) = WB.Worksheets(SheetsToKeep(N)).Name
Next N
Else
ReDim Arr(1 To 1)
Arr(1) = WB.Worksheets(SheetsToKeep).Name
End If
Application.DisplayAlerts = False
On Error Resume Next
For N = WB.Worksheets.Count To 1 Step -1
If IsError(Application.Match(WB.Worksheets(N).Name, Arr, 0)) Then
If VisibleSheetCount(WB) = 1 Then
Exit For
End If
WB.Worksheets(N).Delete
End If
Next N

Application.DisplayAlerts = True

End Sub

Private Function VisibleSheetCount(WB As Workbook) As Long
Dim WS As Worksheet
Dim N As Long
For Each WS In WB.Worksheets
If WS.Visible = xlSheetVisible Then
N = N + 1
End If
Next WS
End Function


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Mon, 22 Feb 2010 01:48:26 -0800 (PST), deltaquattro
wrote:

Hi,

I've written a VBA code which, as an output, adds many sheets to the
current workbook. Before running the code, I'd like to delete all old
sheets in the workbook, save for the worksheets which the user needs
to provide input to the code. So I wrote the following snippet:

Sub DeleteOutputSheets()
Dim sht As Object

' Delete old sheets TODO
For Each sht In Sheets
If sht.name < "Options" And sht.name < "xPlot" Then
'Delete worksheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets(sht.name).Delete
On Error GoTo 0
End If
Next
Set sht = Nothing
' TODO

End Sub

Questions follow:
1. Would you have coded the thing in the same way, e.g. using a For...
Each construct and an If , or do you think there's something quicker/
more robust/etc.?
2. I hardcoded the name of the sheets which must be deleted, which I
reckon Is Not A Good Thing. I'd like to pass an array of strings
InputSheets to the subroutine which contains the name of the sheets to
be spared from deletion (would it be better to pass a collection
containing the sheets?). However, the sheets are always the same, so
I'd like to define InputSheets as a Const in the declaration section
of the module. Alas, that's not possible because Const arrays are not
allowed. What do you suggest? Do I declare InputSheets as a Const and
then I allocate the names in a routine which must be launched at the
very start of the code?

Thanks in advance for your help and feel free to add comments/
suggestions/questions on coding style, structure, etc. if you have
any.

Best Regards

deltaquattro