For Each in range name sheets with codename

[rsjury]

Looking to use a Range from sheet "Employee List" ("A2:A33") and name only sheets with the beginning codename "FTE*".

[TABLE="width: 500"]
<tbody>[TR]
[TD]Codename[/TD]
[TD]Cell[/TD]
[TD]Range[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]FTE01 (FTE01)[/TD]
[TD]A2[/TD]
[TD]Todd[/TD]
[TD]FTE01 (Todd)[/TD]
[/TR]
[TR]
[TD]FTE02 (FTE02)[/TD]
[TD]A3[/TD]
[TD]Steve[/TD]
[TD]FTE02 (Steve)[/TD]
[/TR]
[TR]
[TD]Table (Table)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Renamed[/TD]
[/TR]
[TR]
[TD]FTE03 (FTE03)[/TD]
[TD]A4[/TD]
[TD]Mark[/TD]
[TD]FTE03 (Mark)[/TD]
[/TR]
[TR]
[TD]Report (Report)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Renamed[/TD]
[/TR]
</tbody>[/TABLE]

I know I need to use "For Each in range" and "if FTESheet.Codename like "FTE*" Then". But not sure how to loop through the Range and the Sheets at the same time.

 

[Fluff]

Is this what you're after

Sub renamesht()

    Dim ws As Worksheet
    
    For Each ws In Worksheets
        If ws.Name Like "FTE*" Then
            ws.Name = Sheets("Employee List").Range("A" & Right(ws.Name, 2) + 1)
        End If
    Next ws
        

End Sub

 

[Rick Rothstein]

Your question is not entirely clear, but assuming cells A2 downward contain sheet names (what are shown on the sheet tabs) and assuming you want the code name followed by the sheet name (in parentheses) in Column B but only if the code name starts with "FTE", then give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub GetCodeAndTabNames()
  Dim R As Long
  For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    If Sheets(Cells(R, "A").Value).CodeName Like "FTE*" Then
      Cells(R, "B").Value = Sheets(Cells(R, "A").Value).CodeName & " (" & Cells(R, "A").Value & ")"
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[rsjury]

Thank you both, this one almost gets me there

Here are my changes but I get a "type mismatch" once it gets to "WS.Name = Emp_List.Range

Sub renamesht()

Dim ws As Worksheet

For Each ws In Worksheets
If ws.CodeName Like "FTE*" Then
ws.Name = EMP_LIST.Range("A" & Right(ws.Name, 2) + 1)
End If
Next ws

End Sub

So what I am doing is using the Codename to find the sheet and then renaming the sheet name with the cells from "Emp_List" sheet. This way if I change a name it will go back to that sheet and rename it.

So the codename FTE01 Sheets name is "Todd" Because cell "A2" on EMP_List = Todd, If I change the name in "A2" to "TED", FTE01 Sheet name becomes "TED".

Thanks

 

[Fluff]

What is EMP_LIST?
a Sheet name, a sheet codename, or a range name

 

[rsjury]

What is EMP_LIST?
a Sheet name, a sheet codename, or a range name

The sheet codename that has the list of employees.

Thanks

 

[Fluff]

What are the last 2 characters of ws.name when the error occurs?
It sounds as if they aren't numbers

 

[rsjury]

What are the last 2 characters of ws.name when the error occurs?
It sounds as if they aren't numbers

Run-Time Error 13
Type Mismatch

 

[Rick Rothstein]

Run-Time Error 13
Type Mismatch

Fluff asked you for the last two characters of the worksheet's name when the error occurs... not the error message. When the error occurs, execute this in the VB editor's Immediate Window and post back exactly what gets printed...

Print "<" & Right(ws.Name, 2) & ">"

 

[Fluff]

Missed this

So the codename FTE01 Sheets name is "Todd" Because cell "A2" on EMP_List = Todd, If I change the name in "A2" to "TED", FTE01 Sheet name becomes "TED".

So I suspect that what you need is

Sub renamesht()

    Dim ws As Worksheet
    
    For Each ws In Worksheets
        If ws.CodeName Like "FTE*" Then
            ws.Name = Sheet7.Range("A" & Right[COLOR=#ff0000](ws.CodeName[/COLOR], 2) + 1)
        End If
    Next ws

End Sub