【孙兴华vba第一季】E3&E4-提取工作表名称并指定删除-个人解法

推荐孙老师的vba课程
《跟着孙兴华学习Excel VBA 第一季》

题目：删除指定工作表

基本思路为获取工作表名称，然后在对应名称后面写入删除，最后进行批量删除

老师解法

1. 获取工作表名称写入第一列

Sub GetShtByVba()
    Dim sht As Worksheet, k As Long
    Application.ScreenUpdating = False
    k = 1
    Range("a:b").Clear '清空数据
    Range("a:a").NumberFormat = "@" '设置文本格式
    For Each sht In Worksheets '遍历工作表取表名
        k = k + 1
        Cells(k, 1) = sht.Name
    Next
    Range("a1:b1") = Array("工作表名", "是否删除")
    Application.ScreenUpdating = True
End Sub

2. 指定删除的工作表并删除

Sub DelShtByVba()
    Dim sht As Worksheet, i As Long, r
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    r = Range("a1").CurrentRegion '数据装入数组r
    For i = 2 To UBound(r) '遍历并删除工作表
        If r(i, 2) = "删除" Then Worksheets(CStr(r(i, 1))).Delete
    Next
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

老师的解法已经非常简洁易懂

个人解法

这里调皮一下，通过输入工作表名称进行删除

Public Sub 删除指定工作表()
    Dim a
    Dim sht As Worksheet
    Application.DisplayAlerts = False
    On Error Resume Next
    Do
        a = Application.InputBox("Input the sheet you want to delete:", Type:=2)
        If a <> "" Then
            For Each sht In Worksheets
                If a = sht.Name Then
                    sht.Delete
                    Exit For
                    '删除工作表后退出For循环，但不推出Do循环
                End If
            Next
        Else
            Exit Do
            '输入为空，退出Do循环
        End If
    Loop
    Application.DisplayAlerts = True
End Sub