Amend formula if WS name like

[harveyldj93]

I am trying to write a Macro which will correct all the worksheets that contain the phrase Perm, this has a number first i.e 0000 Perm.
The code I have written is below, however it only seems to amend the first tab. Does anyone know why it isn't cycling through?

If ws.Name Like "*Perm" Then
For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "*Perm" Then
ActiveSheet.Unprotect "****"
Range("M261:M269").Select
Selection.Replace What:="T", Replacement:="U", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Protect "****"
End If
Next ws
End Sub

Appreciate it!

 

[Joe4]

Welcome to the Board!

Note that looping through the worksheet names does NOT actually select/activate them.
So all range references without a worksheet component only apply to the sheet you are on when you started the macro (that is the "ActiveSheet").

You can either add worksheet references to all of your ranges, or activate the sheet, i.e.

If ws.Name Like "*Perm" Then
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name Like "*Perm" Then
            ws.Activate
            ActiveSheet.Unprotect "****"
            Range("M261:M269").Replace What:="T", Replacement:="U", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            ActiveSheet.Protect "****"
        End If
    Next ws
End If

End Sub

 

[harveyldj93]

Welcome to the Board!

Note that looping through the worksheet names does NOT actually select/activate them.
So all range references without a worksheet component only apply to the sheet you are on when you started the macro (that is the "ActiveSheet").

You can either add worksheet references to all of your ranges, or activate the sheet, i.e.

If ws.Name Like "*Perm" Then
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name Like "*Perm" Then
            ws.Activate
            ActiveSheet.Unprotect "****"
            Range("M261:M269").Replace What:="T", Replacement:="U", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            ActiveSheet.Protect "****"
        End If
    Next ws
End If

End Sub

Thats perfect! Thanks very much for your help.

 

[Joe4]

You are welcome.

I hope the explanation makes sense.
It is a common mistake that people make, not realizing that looping through the sheet names does not actually activate each sheet.