Looping through worksheets

[OfficeUser]

I am using this to try and loop through all worksheets and change one cell. Problem is it only will alter the active sheet from which I was on when I trigger the macro. Any suggestions?

Sub ChangeAll()
    Dim Current As Worksheet
         For Each Current In Worksheets
            ActiveSheet.Unprotect Password:="password"
            Range("B6").Value = "Off"
            ActiveSheet.Protect Password:="password"
         Next
End Sub

 

[Jonmo1]

In that structure, Current becomes a worksheet object representing each sheet during the loop.
So use that in the code.

Change each instance of ActiveSheet to Current
And add current to the range..
Current.Range("B6").Value = "Off"

 

[sykes]

Try:

Sub ChangeAll()

Dim Current As Worksheet
    
With Current
         For Each Current In Worksheets
            .Unprotect Password:="password"
            .Range("B6").Value = "Off"
            .Protect Password:="password"
         Next
End With
End Sub

 

[OfficeUser]

Try:

Sub ChangeAll()
 
Dim Current As Worksheet
 
With Current
         For Each Current In Worksheets
            .Unprotect Password:="password"
            .Range("B6").Value = "Off"
            .Protect Password:="password"
         Next
End With
End Sub

I tried this and received an error
Runtime error 91: Object variable or With block variable not set.

 

[OfficeUser]

I figured it out since. I followed jonmo1's instructions and all worked out well. Thanks to you both for the help.

 

[sykes]

.......just for the record - in case other users use this thread........

I should have put the With statement inside the loop......

This now works:

Sub ChangeAll()

Dim Current As Worksheet
 
     For Each Current In Worksheets
        With Current
            .Unprotect Password:="password"
            .Range("B6").Value = "Off"
            .Protect Password:="password"
        End With
     Next

End Sub