Type mismatch: How to clear contents for all worksheets except ones specified?-CodePudding

I have many sheets in the same workbook that I would like to clear before performing the rest of the macro.

I have been using the following:

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

How can I use a for loop to minimize the amount of lines used?

In all there are 4 sheets whose contents I do not want cleared.

This is where I'm at:

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

CodePudding user response：

I would use 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

CodePudding user response：

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

CodePudding user response：

You can try this

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