Solved: Code not updating all worksheets

[dakkat]

Hello,

I have written the following code and it works but only for the active worksheet. I want the code to cycle through all worksheets. If I make a worksheet active (by clicking the tab) and run the macro it works for that active worksheet. There are hundreds of worksheets. Can someone help me with why it is not looking at all worksheets? What am I missing?

[vba]Sub ClearExc()

Application.ScreenUpdating = False

Dim sh As Worksheet

For Each sh In Worksheets
If LCase(sh.Name) <> "admin" Then
If InStr(Range("D4").Value, "@") Then
Range("A120:I500").Select
Selection.ClearContents
Range("A120").Select
End If
End If
Next

Application.ScreenUpdating = True

End Sub
[/vba]

[Paul_Hossler]

You need to specify the sheet on Range() 's

Without the worksheet, it assumes the ActiveSheet

Also, not necessary to Select something to act on it

[vba]

Sub ClearExc()

Application.ScreenUpdating = False

Dim sh As Worksheet

For Each sh In Worksheets
If LCase(sh.Name) <> "admin" Then
If InStr(sh.Range("D4").Value, "@") Then
sh.Range("A120:I500").ClearContents
sh.Range("A120").Select
End If
End If
Next

Application.ScreenUpdating = True

End Sub

[/vba]

Paul

PS. Not tested

[dakkat]

Paul, Thank you. That worked perfectly.