基于多个工作表标准删除工作表 [英] Deleting Worksheets Based on Multiple Worksheet Criteria

问题描述

我正在继续构建与从主机检索的数据相关的宏，类似于我在

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

当我处理数据集时，我的用户似乎可以删除一旦导出到他们的选定目的地。但是，为了方便使用，我试图保留与我的宏相关的那些表格。对于 OR 条件，保留的张数超过2，为了讨论目的可能最多为10个。我目前的代码是从这个网站改编而成。我还查看了这个 SO文章。

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.

我的代码如下：

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.

除此之外，给定可能的条件数量，我也试图确定列表，集合甚至甚至可以使用一个数组（给定循环），而不是一个巨大的 OR 列表，这对我来说似乎很笨拙。

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.

推荐答案

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

基于数组的方法：

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

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

这篇关于基于多个工作表标准删除工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！