Automatically Name Tabs from List on Worksheet Named Team

[Guest]

I am wanting to automatically name all sheet tabs from a list I have created
on a worksheet named Team. I have set up 18 sheets currently named Sheet1
through Sheet18 (besides the first two named Team and Stats).

I would like to have each tab renamed respectively according to the list I
have on the Team tab in cells A1 through A18.

I have tried a couple other tips on here but they merely add more sheets and
I have data on the current sheets that I want to rename.

Thanks,
Cheri

 

[PCLIVE]

I would use VBA for this.

Sub NameSheets()

For sh = 1 To 18
Sheets(sh + 2).Name = Range("A" & sh).Value
Next sh

End Sub

HTH,
Paul

 

[Don Guillett]

See if this idea helps

Sub namesheets()
For i = 1 To 18
Sheets("sheet" & i).Name = Cells(i, "a")
Next i
End Sub

 

[Guest]

Wow! You made it so easy!!!

Thank you

I would use VBA for this.

Sub NameSheets()

For sh = 1 To 18
Sheets(sh + 2).Name = Range("A" & sh).Value
Next sh

End Sub

HTH,
Paul

 

[Cheri]

Hi Don,

Thank you such much for your response. I am sorry I didn't see it before!
I appreciate how simple you made this!!!

 

[Cheri]

I have 20 worksheets but there will not always be 20 names in the list. What
code can I add that will stop the macro if it runs out of names in the list
so that the "debug" error does not pop up?

Also, is there a way that once the worksheets have been renamed, if the list
changes for any reason (new employees, etc.) the macro will still name the
tabs from the new list even though the tabs are not named worksheet1,
worksheet2, etc.?

 

[Dave Peterson]

Sub namesheets()
dim i as long
For i = 1 To 20
if cells(i,"A").value = "" then
exit for 'get out of the loop
end if
Sheets(i).Name = Cells(i, "a").value
Next i
End Sub

It'll name the leftmost sheet the top name. The next sheet will get the 2nd
name and so forth.

 

[Cheri]

That is perfect!!!! Thank you so much. Now, can I have the code that would
change them all back to Sheet1, Sheet2, etc.?

I am so sorry for being such a pain!

Cheri

 

[Cheri]

Another question Dave. Sheets 1 and 2 are named Team and Stats. I do not
want their tab names changed. I changed the "For i" statement to read "=3 To
23" and that worked great, other than it skipped the first two names in the
list.

What did I miss here?

Thanks again,
Cheri

 

[Dave Peterson]

Sub namesheets()
dim i as long
For i = 3 To 23
if cells(i-2,"A").value = "" then
exit for 'get out of the loop
end if
Sheets(i).Name = Cells(i-2, "a").value
Next i
End Sub

So avoid renaming Team and Stats and start with Sheet1???

Sub namesheets2()
dim i as long
For i = 3 To worksheets.count
if sheets(i).name = "Sheet" & i - 2
Next i
End Sub

 

[Chersie]

You have it exactly right!!! Thank you so much!

Cheri