如何使用vba删除重复的工作表

Question

假设我有3个工作表"Asutosh"Asutosh2"Asutosh3"；我想删除"；Asutosh2"；以及"；Asutosh3"；使用vba。

我使用了vba，但我必须为其他名称手动执行，例如如果我为Asutosh录制，其他额外的重复工作表不会删除。

Answer 1

有两种方法可以使用。

如果您希望有一个主工作表，它不会删除，但每隔一个工作表都会删除，请使用类似于下面的内容。

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Closes all other worksheets par "Asutosh", saves user time not having to delete imported sheets everytime
For Each ws In ThisWorkbook.Worksheets
Application.DisplayAlerts = False
If ws.Name <> "Asutosh" Then 'If a worksheet is not named "Asutosh" it gets deleted
ws.Delete
End If
Next ws
Application.DisplayAlerts = True
MsgBox ("All sheets are deleted except specific sheet - After this, you can click either 'Save' or 'Don't Save' button") 'Message box to reasure user is okay with either option when closing the file

End Sub

在VBA编辑器中将此^添加到此工作簿

或者，如果您希望删除具有特定名称的工作表，请使用以下

Sub vba_delete_sheet()
Sheets("Asutosh2").Delete
Sheets("Asutosh3").Delete
End Sub

希望这能有所帮助！

Answer 2

这样的方式是可能的

Sub DelDubl()
Dim wsh As Worksheet, j As Long, wshNm
' array with names of worksheets which are not to delete
wshNm = Array("Asutosh")
For Each wsh In ThisWorkbook.Worksheets

For j = LBound(wshNm) To UBound(wshNm)
If wsh.Name Like wshNm(j) Then GoTo NXT1
Next j

Application.DisplayAlerts = False
wsh.Delete
Application.DisplayAlerts = True
NXT1:
Next

End Sub