Clearing multiple sheets VBA

[Mr2017]

Hi

The code below works with only one condition in the IF statement (the first one - Summary sheet).

However, when I add the other two sheet names that should NOT be cleared, it doesn't work?

Do I need to change something in the IF statement?

Thanks in advance.

Code that works:

Sub ClearSheets()
 
    Dim sht As Worksheet
    
    For Each sht In ThisWorkbook.Worksheets
    
        If sht.Name <> "Summary" Then
          sht.Cells.ClearContents
          
        End If
    Next


Exit Sub


End Sub

Code that doesn't work:

Sub ClearSheets()
 
    Dim sht As Worksheet
    
    For Each sht In ThisWorkbook.Worksheets
    
        If sht.Name <> "Summary" And sht.Name <> "Sheet2" And sht.Name <> "Sheet3" Then
          sht.Cells.ClearContents
          
        End If
    Next


Exit Sub


End Sub

 

[Gerald Higgins]

Maybe something like this ?

    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case "Summary", "Sheet2", "Sheet3"
            GoTo NEXTWS
        Case Else
            sht.Cells.ClearContents
    End Select
NEXTWS:
Next ws

Edit to add - I'm not a code expert, there may be better ways of doing this !

 

[footoo]

Check that the actual sheet names are exactly the same as the names in your macro (e.g. any spaces?)

 

[Mr2017]

Hi Gerald

This code gave me a Compile error: "Invalid Next Control variable reference"

Then it highlighted the WS in this line of the code (ie the second last line / just before the End Sub bit)

Next Ws

Do you know why that would be?

 

[Mr2017]

Hi footoo

Yes, the sheet names are exactly the same as the names in the macro....

 

[Fluff]

Try this

Sub ClearSheets()
 
    Dim sht As Worksheet
    
    For Each sht In ThisWorkbook.Worksheets
    
        If sht.Name <> "Summary" And sht.Name <> "Sheet2" And sht.Name <> "Sheet3" Then
          Debug.Print "|" & sht.Name & "|"
          sht.Cells.ClearContents
          
        End If
    Next
End Sub

In the Immediate window it will print a list of sheet names that it's trying to clear. Copy & paste that list to the thread.
If the immediate window is not visible (normally below the code window) Ctrl G, will bring it up

 

[Mr2017]

Hmmm....

Interesting!

That code worked!

The list of other sheets is quite long! Over 50! But it shows the sheets that it cleared, which is good!

And more importantly, didn't clear the sheets that I didn't want it to clear!

Thanks Fluff!

I must have made a typo!

 

[Fluff]

Glad it's working & thanks for the feedback

 

[Mr2017]

You're welcome!

Thank you!