vba Excel使用代号删除除活动工作表和指定工作表之外的所有工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41746153/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel delete all sheets except active sheet and specified sheets using codenames
提问by luke
What would be the best way to delete all the sheets in the active Workbook, except the selected/active sheet and 4 specified sheets, using their CodeNames?
使用它们的代码名称删除活动工作簿中的所有工作表的最佳方法是什么，除了选定/活动工作表和 4 个指定工作表？
I've come up with this code and it works, but it seems like there would be a better way to do it:
我想出了这段代码并且它有效，但似乎有更好的方法来做到这一点：
Sub delete_test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Deletes all sheets except ActiveSheet and specified sheets using codenames
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Select Case ws.CodeName
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
Case Else
With ws
If ws.NAme <> ThisWorkbook.ActiveSheet.NAme Then
ws.Delete
End If
End With
End Select
Next
End Sub
回答by Scott Craner
Your code is already fairly concise.
你的代码已经相当简洁了。
You can add the ThisWorkbook.ActiveSheet.Nameto the first Case and avoid the IF.
您可以将ThisWorkbook.ActiveSheet.Name加到第一个 Case 并避免 IF。
Also the With Block is not needed as you are only doing one thing. The extra typing is more than simply referring to the sheet.
此外，不需要 With Block，因为您只做一件事。额外的打字不仅仅是简单地引用工作表。
Make sure you turn your alerts back on.
确保重新打开警报。
Sub delete_test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Deletes all sheets except ActiveSheet and specified sheets using codenames
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Select Case ws.CodeName
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", ThisWorkbook.ActiveSheet.CodeName
Case Else
ws.Delete
End Select
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
回答by Shai Rado
You could check if wsis Not ActiveSheet, and then check if CodeNameis not one of your criteria.
您可以检查是否ws是Not ActiveSheet，然后检查是否CodeName不是您的标准之一。
Sub delete_test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Deletes all sheets except ActiveSheet and specified sheets using codenames
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If Not Worksheets(ws.Name) Is ActiveSheet Then '<-- first check if sheet is not active sheet
Select Case ws.CodeName
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
Case Else
ws.Delete
End Select
End If
Next ws
End Sub
