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]

Ah, good thought!

I do get them occasionally

 

[Rick Rothstein]

Missed thisSo 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

Using ws.CodeName is a good thought; however, I think the +1 might be wrong. I must admit I still am not entirely clear what the OP is trying to do, but won't adding one to the number at the end of the code name end up naming the code name's worksheet name with the wrong person's name? In other words, using the OP's original list, if sheet whose code name is FTE01 is current named Todd, won't it become Steve afterwards? I'm thinking he may just be asking to rename it from FTE01 to Todd (although I am not entirely certain how the name, not code name, became FTE01 in the first place).

 

[Fluff]

My understanding is that the OP has a list of names starting in A2, going down.
So if the codename is is FTE01, we need to get the name in A2, ie 01+1

 

[rsjury]

Missed thisSo 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

I ran the immediate window and this is what I get
<on>
<tt>
<E3>
<E4>
<E5>
<E6>
<E7>
<E8>
<E9>
<10>
<11>
<12>
<13>
<14>
<15>
<16>
<17>
<18>
<19>
<20>
<21>
<22>
<23>
<24>
<25>
<26>
<27>
<28>
<29>
<30>
<31>
<32>

FYI, I am not trying to include the codename or number in the sheet name. all I am doing is using the codename to make sure other sheets are not being renamed. So Codename FTE01 = Sheetname "Todd", Codename FTE02 = Sheetname "Ron". These sheetnames are pulled from the Range in Codename EMP_List (Which is the sheet of Sheet names) A2:A33. IF I change the names in the list A2:A33 they should change the sheet name. Change "Todd" to "Scott" would rename codname FTE01 to Sheetname "Scott".

 

[Rick Rothstein]

My understanding is that the OP has a list of names starting in A2, going down.
So if the codename is is FTE01, we need to get the name in A2, ie 01+1

Ah, yes, I see now.

 

[Fluff]

@rsjury
Did you try the modified code that you quoted in post#14, and if so did it work?
If not, in all your posts you've been quoting codenames like FTE01, FTE02, but in the list the first value is 10. which is correct?

 

[rsjury]

@rsjury
Did you try the modified code that you quoted in post#14, and if so did it work?
If not, in all your posts you've been quoting codenames like FTE01, FTE02, but in the list the first value is 10. which is correct?

Yeah I tried the one I changed and it had the same error and I do have FTE01 up to FTE32 as sheet codenames.

To answer the other question, The sheets default to FTE01 (FTE01), FTE02 (FTE02). This is the default based on a formula if no name is entered in a a cells A2:A33. So potentially you could have A2=Todd A3=FTE02, if A3 has no name entered.

I really appreciate your efforts, in the past I did it this way and could go back to it if needed.

FTE01.Name = EMP_List.Range ("A2")
FTE02.Name = EMP_List.Range ("A3")
FTE03.Name = EMP_List.Range ("A4")
Etc..

Thanks, RJ

 

[Rick Rothstein]

Yeah I tried the one I changed and it had the same error and I do have FTE01 up to FTE32 as sheet codenames.

To answer the other question, The sheets default to FTE01 (FTE01), FTE02 (FTE02). This is the default based on a formula if no name is entered in a a cells A2:A33. So potentially you could have A2=Todd A3=FTE02, if A3 has no name entered.

I really appreciate your efforts, in the past I did it this way and could go back to it if needed.

FTE01.Name = EMP_List.Range ("A2")
FTE02.Name = EMP_List.Range ("A3")
FTE03.Name = EMP_List.Range ("A4")
Etc.

This is Fluff's code from Message #10 where I change Sheet7 to EMP_LIST... after changing the CodeNames of a few sheets to FTE## (in sequential order) and changing the CodeName for the sheet with the new sheet names to EMP_LIST, the code worked fine for me.

[table="width: 500"]
[tr]
	[td]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.CodeName, 2) + 1)
        End If
    Next ws

End Sub[/td]
[/tr]
[/table]

 

[rsjury]

This is Fluff's code from Message #10 where I change Sheet7 to EMP_LIST... after changing the CodeNames of a few sheets to FTE## (in sequential order) and changing the CodeName for the sheet with the new sheet names to EMP_LIST, the code worked fine for me.

[TABLE="width: 500"]
<tbody>[TR]
[TD]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.CodeName, 2) + 1)
        End If
    Next ws

End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks Guys, I am off to catch a plane. I will relook at this in a week when I get back. BTW I really like this board and how you guys take time to help people!

Thanks, RJ
T