类型不匹配:如何清除除指定的所有工作表的内容?

Question

在执行宏的其余部分之前，我想在同一工作簿中清除许多工作表。

我一直在使用以下内容:

Sheets("1710").Select
Sheets("1710").Cells.Select
Selection.ClearContents
Sheets("BPL1710").Activate
Sheets("BPL1710").Cells.Select
Selection.ClearContents
Sheets("1711").Select
Sheets("1711").Cells.Select
Selection.ClearContents
Sheets("BPL1711").Activate
Sheets("BPL1711").Cells.Select
Selection.ClearContents
Sheets("1713").Select
Sheets("1713").Cells.Select
Selection.ClearContents
Sheets("BPL1713").Activate
Sheets("BPL1713").Cells.Select
Selection.ClearContents
Sheets("1714").Select
Sheets("1714").Cells.Select
Selection.ClearContents
Sheets("BPL1714").Activate
Sheets("BPL1714").Cells.Select
Selection.ClearContents
Sheets("1715").Select
Sheets("1715").Cells.Select
Selection.ClearContents
Sheets("BPL1715").Activate
Sheets("BPL1715").Cells.Select
Selection.ClearContents
Sheets("1716").Select
Sheets("1716").Cells.Select
Selection.ClearContents
Sheets("BPL1716").Activate
Sheets("BPL1716").Cells.Select
Selection.ClearContents
Sheets("1717").Select
Sheets("1717").Cells.Select
Selection.ClearContents
Sheets("BPL1717").Activate
Sheets("BPL1717").Cells.Select
Selection.ClearContents

如何使用for循环来最小化所使用的行数?

总共有4张表的内容我不想清除。

这是我在哪里:

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Or "RawData" Or "TheButton" Then
ws.Cells.Select
Selection.ClearContents
Else
End If
Next

Answer 1

我会用Select...Case

For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Sheet1", "RawData", "TheButton"
' skip
Case Else
ws.Cells.ClearContents
End Select
Next

Answer 2

Try below loop-

Sub ClearSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If (ws.Name <> "Sheet1") Or (ws.Name <> "RawData") Or (ws.Name <> "TheButton") Then
ws.Cells.ClearContents
End If
Next
End Sub

Answer 3

你可以试试这个

Option Explicit
Sub ClearSheets()
'Clear All Except One Sheet
Dim ws As Worksheet
For Each ws In Application.ActiveWorkbook.Worksheets
If ws.Name <> " Your Specific Sheet Name Here " Then 
ws.Cells.Clear
End If
Next
End Sub