PDA

View Full Version : Solved: Code not updating all worksheets



dakkat
02-02-2011, 03:06 PM
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?

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

Paul_Hossler
02-02-2011, 06:37 PM
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



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


Paul

PS. Not tested

dakkat
02-03-2011, 01:21 AM
Paul, Thank you. That worked perfectly.