Looping through worksheets, excluding certain ws's

[Jon_H]

Hi all,

I've pulled together this code from various other posts but something must be wrong as it's not actually moving through the worksheets! I'd welcome anyone's suggestions! Thanks in advance!

Sub Clear_consumption_from_CheckingTool()Dim sh As Worksheet


For Each sh In ActiveWorkbook.Worksheets
Select Case sh.Name
Case Is = "Sheet 1", "Sheet 2", "Sheet 3"
Case Else
Range("D4:BA40,D48:BA84").Select
Selection.ClearContents
End Select
Next sh
End Sub

 

[dmt32]

Hi,
you have not qualified the Range to your object variable (shown in RED)

see if this update helps

Sub Clear_consumption_from_CheckingTool()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
    Select Case sh.Name
    Case Is = "Sheet 1", "Sheet 2", "Sheet 3"
    Case Else
        sh.Range("D4:BA40,D48:BA84").ClearContents
    End Select
    Next sh
End Sub

Also, you seldom need to select a sheet in code.


Hope helpful

Dave

 

[Jon_H]

Thanks Dave; that's worked a treat! Every day's a school day