excel - 删除Excel工作表(如果不在数组中)

我在解决错误“时间执行错误＃13：类型不兼容”时遇到问题。如果用户创建了一些未在数组中声明的工作表，它将被删除。有人可以帮忙吗？

sub DeleteNewSheets()

Dim ws, wsP As Worksheet
Dim ArrayOne As Variant

    Application.DisplayAlerts = False

    ArrayOne = Array("SheetA", "SheetB", "SheetC", "Sheet_n")


    Set wsP = ThisWorkbook.Worksheets(ArrayOne) ' <--- ERROR #13

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsP.Name Then ws.Delete
    Next ws

    Application.DisplayAlerts = True

end sub

最佳答案

您的代码行说：

Set wsP = ThisWorkbook.Worksheets(ArrayOne)

试图将一个工作表对象设置为包含多个工作表的数组。这就像试图将单个Integer设置为Integer的数组一样。

尝试使用以下代码

Sub DeleteNewSheets()

    Dim ws As Worksheet
    Dim ArrayOne() As Variant
    Dim wsName As Variant
    Dim Matched As Boolean

    ArrayOne = Array("SheetA", "SheetB", "SheetC", "Sheet_n")

    Application.DisplayAlerts = False

    For Each ws In ThisWorkbook.Worksheets
        Matched = False
        For Each wsName In ArrayOne
            If wsName = ws.Name Then
                Matched = True
                Exit For
            End If
        Next
        If Not Matched Then
            ws.Delete
        End If
    Next ws

    Application.DisplayAlerts = True

End Sub