excel - 删除Excel中的特定选项卡

Question

我有一个包含多个工作表的工作簿。我想删除所有没有“ApprovedSS”字样的选项卡。目前我有以下代码:

Sub DeleteTabs()
Dim xWs As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts  = False
For Each xWs In Application.ActiveWorkbook.Worksheets
    If xWs.Name <> "ApprovedSS" Then
        xWs.Delete
    End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

结束子

我可以修改此代码来完成工作吗？

Answer 1

代替这个，它测试等价性(或其否定):

If xWs.Name <> "ApprovedSS" Then
    xWs.Delete
End If

试试这个，它测试工作表的名称是否包含 “ApprovedSS”:

If Instr(xWs.Name, "ApprovedSS") > 0 Then
    xWS.Delete
End If

要处理多个工作表，请首先构建要保留的工作表名称的分隔列表，将其拆分为一个数组，然后使用 Match 函数测试每个工作表名称是否存在于数组中.

这会测试 KEEP_LIST 变量中列出的工作表名称的完全匹配。

Option Explicit

Sub DeleteSheets()
Dim KEEP_LIST As String
Dim keepSheets
Dim ws As Worksheet

KEEP_LIST = "ApprovedSS,ApprovedRT" '## Add items to this list, separated by comma
keepSheets = Split(KEEP_LIST, ",")

For Each ws In ThisWorkbook.Worksheets
    'You can't delete the last sheet in a book, so don't even try.
    If ThisWorkbook.Worksheets.Count = 1 Then Exit For
    ' if the sheetname doesn't exist in our KEEP_LIST,
    ' then we delete this worksheet
    If IsError(Application.Match(ws.Name, keepSheets, False)) Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If
Next
End Sub