excel - 如何删除 VBA 中的某些工作表以外的工作表

我有这段代码,但是当我运行它时 excel 总是崩溃。我没有收到错误代码或任何东西。 Excel 刚刚关闭。

Sub DeleteSheets()
    Dim xWs As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> "Overview" And xWs.Name <> "Models-Features" And xWs.Name <> "Formulas" And xWs.Name <> "Original Features" Then
            xWs.Delete
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

回答1

看来您的代码正在删除所有导致错误的工作表,即使其中一个标记为“概述”。我使用 Select 清理了它,它现在不会删除适当的命名表:

Sub DeleteSheets()
    Dim xWs As Worksheet
    Application.EnableEvents = False
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In Application.ActiveWorkbook.Worksheets
        If Sheets.Count = 1 Then Exit For
        Select Case xWs.Name
            Case "Overview"
            Case "Models-Features"
            Case "Formulas"
            Case "Original Features"
            Case Else
                xWs.Delete
        End Select
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlAutomatic
End Sub

Edit1:添加了对 Sheets.Count 的检查,这将防止由于删除工作簿中的最后一张工作表而导致的错误。

Edit2:添加了额外的 application 约束

相似文章