excel - 尝试删除除特定工作表之外的所有工作表时出错

Question

我想删除工作簿中的所有工作表，除了给定年份的月末工作表，例如工作表名称所有工作表名称都以这种格式输入 dd.mm.yy
我尝试了其他代码，例如 case 而不是 If 但所有代码似乎都停在 ws.delete

Sub Delete_Sheets
    Yr = InputBox("Use YY format only.", "Which year to keep?", 18)

    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "31.01.Yr" Or ws.Name <> "28.02.Yr" Or ws.Name <> "31.03.Yr" Or ws.Name <> "30.04.Yr" Or ws.Name <> "31.05.Yr" Or ws.Name <> "30.06.Yr" Or ws.Name <> "31.07.Yr" Or ws.Name <> "31.08.Yr" Or ws.Name <> "30.09.Yr" Or ws.Name <> "31.10.Yr" Or ws.Name <> "30.11.Yr" Or ws.Name <> "31.12.Yr" Then        
            ws.Delete
        End If
    Next

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True    
End Sub

Answer 1

使用Application.InputBox method而不是 InputBox只要。这个有一个 Type:=1强制用户仅输入数字的参数。

确保您测试 ThisWorkbook.Worksheets.Count > 1因为您无法删除最后一个工作表。必须至少保留 1 个工作表。

将所有要跳过的工作表放入数组SkipSheets并为您的工作表名称过滤该数组(UBound(Filter(SkipSheets, ws.Name)) > -1)

Option Explicit

Public Sub DeleteSheets()
    Dim InputYear As Variant
    InputYear = Application.InputBox(Prompt:="Use YY format only.", Title:="Which year to keep?", Default:=18, Type:=1)

    If VarType(InputYear) = vbBoolean And InputYear = False Then Exit Sub 'user pressed cancel

    Dim SkipSheets() As Variant
    SkipSheets = Array("31.01." & InputYear, "28.02." & InputYear, "31.03." & InputYear, "30.04." & InputYear, "31.05." & InputYear, "30.06." & InputYear, "31.07." & InputYear, "31.08." & InputYear, "30.09." & InputYear, "31.10." & InputYear, "30.11." & InputYear, "31.12." & InputYear)

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If Not (UBound(Filter(SkipSheets, ws.Name)) > -1) And ThisWorkbook.Worksheets.Count > 1 Then
            ws.Delete
        End If
    Next ws

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub