vba - Deleting Worksheets Based on Multiple Worksheet Criteria

Question

I am continuing to build on macros related to data retrieved from mainframe, similar to what is mentioned in my previous questions.

I am generating a large number of sheets, using a macro extension based on my autofilter criteria discussed here. For reference purposes, a condensed version of this code is as follows:

Sub AddSheets()

Dim RngOne As Range, cell As Range

For Each cell In RngOne

Sheets.Add After:=Sheets(1)

Sheets(2).Select

Sheets(2).Name = cell.Value

Next


End Sub

As I work through the datasets, it appears it may be expedient for my users to delete the sheets generated once they are exported to their selected destination. However, for ease of use, I am attempting to retain those sheets which are relevant to my macro. The number of sheets retained exceeds 2 for the OR condition, and may be up to 10 or more for discussion purposes. My current code is adapted from this site. I also reviewed this SO article.

My code is as follows:

Sub DeleteAllButNotedSheets()

Dim IndividualWorkSheet As Worksheet

Application.DisplayAlerts = False

For Each IndividualWorkSheet In ThisWorkbook.Worksheets

    If IndividualWorkSheet.Name <> "Sheet1" Or "Criteria" Or "TemplateSheet" Or "TemplateSheet2" Then
        IndividualWorkSheet.Delete
    End If

Next

Application.DisplayAlerts = True

End Sub

Currently, the code results in a Type Mismatch error. I am seeking a fix to this error dilemma.

In addition to this, given the number of possible conditions, I am also attempting to determine if a list, collection or even an array (given the loop) can be used instead of a giant OR list, which seems clumsy to me. I am seeking a fix or even advice to get started with regard to this list issue.

Answer 1

tmp = IndividualWorkSheet.Name  
If tmp <> "Sheet1" And tmp <> "Criteria" And _
   tmp <> "TemplateSheet" And tmp <> "TemplateSheet2" Then 
      IndividualWorkSheet.Delete     
End If

Array-based approach:

Dim arr
arr = Array("Sheet1", "Criteria", "TemplateSheet", "TemplateSheet2")

If IsError(Application.Match(IndividualWorkSheet.Name, arr, 0)) Then
    IndividualWorkSheet.Delete    
End If