Clearing multiple sheets VBA

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:
Code:
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:

Code:
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Maybe something like this ?

Code:
    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 !
 
Last edited:

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
Check that the actual sheet names are exactly the same as the names in your macro (e.g. any spaces?)
 
Last edited:

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi footoo

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
Try this
Code:
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

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
Glad it's working & thanks for the feedback
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You're welcome!

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,272
Messages
5,527,710
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top