需求:工作文件中通常会加入一些辅助表,这些表在交付时一般要删除。每张表要经过定位、右击删除、点击确认、等待程序执行删除等步骤才能完成删除动作。假设每张表的删除耗时6秒,删除8张表差不多要1分钟,而运行代码仅需数秒(效率相差10倍以上)。
代码:
Sub removeExtraSheets()
'
' remove extra sheets from workbook to prepare deliverable
'
Dim sht As Worksheet
Application.DisplayAlerts = False
For Each sht In Worksheets
If sht.Name <> "1" And _
sht.Name <> "2" And _
sht.Name <> "3" And _
sht.Name <> "4" And _
sht.Name <> "5" And _
sht.Name <> "6" And _
sht.Name <> "7" And _
sht.Name <> "8" And _
sht.Name <> "9" And _
sht.Name <> "10" And _
sht.Name <> "11" Then
sht.Delete
End If
Next sht
Application.DisplayAlerts = True
Sheets("Raw Data").Range("A1").Select
End Sub
其中,工作表1到11均为需要保留的工作表,其余工作表都将被删除。