vba 根据多个工作表条件删除工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11888291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Deleting Worksheets Based on Multiple Worksheet Criteria
提问by HymanOrangeLantern
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 ORcondition, 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.
在我处理数据集时，似乎我的用户删除导出到所选目的地后生成的工作表可能是权宜之计。但是，为了便于使用，我试图保留那些与我的宏相关的工作表。对于OR条件，保留的页数超过 2 ，并且出于讨论目的可能会达到 10 或更多。我当前的代码改编自这个站点。我还回顾了这篇SO 文章。
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 ORlist, which seems clumsy to me. I am seeking a fix or even advice to get started with regard to this list issue.
除此之外，考虑到可能的条件数量，我还试图确定是否可以使用列表、集合甚至数组（给定循环）代替巨型OR列表，这对我来说似乎很笨拙。我正在寻求修复甚至建议以开始解决此列表问题。
回答by Tim Williams
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
