Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
See if this idea helps
Sub namesheets() For i = 1 To 18 Sheets("sheet" & i).Name = Cells(i, "a") Next i End Sub -- Don Guillett SalesAid Software "Cheri" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
Wow! You made it so easy!!!
Thank you "PCLIVE" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
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!!! "Don Guillett" wrote: See if this idea helps Sub namesheets() For i = 1 To 18 Sheets("sheet" & i).Name = Cells(i, "a") Next i End Sub -- Don Guillett SalesAid Software "Cheri" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
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.? "Don Guillett" wrote: See if this idea helps Sub namesheets() For i = 1 To 18 Sheets("sheet" & i).Name = Cells(i, "a") Next i End Sub -- Don Guillett SalesAid Software "Cheri" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
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 wrote: 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.? "Don Guillett" wrote: See if this idea helps Sub namesheets() For i = 1 To 18 Sheets("sheet" & i).Name = Cells(i, "a") Next i End Sub -- Don Guillett SalesAid Software "Cheri" wrote in message ... 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
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 "Dave Peterson" wrote: 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 wrote: 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.? "Don Guillett" wrote: See if this idea helps Sub namesheets() For i = 1 To 18 Sheets("sheet" & i).Name = Cells(i, "a") Next i End Sub -- Don Guillett SalesAid Software "Cheri" wrote in message ... 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
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" wrote: 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 wrote: 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.? "Don Guillett" wrote: See if this idea helps Sub namesheets() For i = 1 To 18 Sheets("sheet" & i).Name = Cells(i, "a") Next i End Sub -- Don Guillett SalesAid Software "Cheri" wrote in message ... 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
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 Cheri wrote: 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" wrote: 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 wrote: 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.? "Don Guillett" wrote: See if this idea helps Sub namesheets() For i = 1 To 18 Sheets("sheet" & i).Name = Cells(i, "a") Next i End Sub -- Don Guillett SalesAid Software "Cheri" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Name Tabs from List on Worksheet Named Team
You have it exactly right!!! Thank you so much!
Cheri "Dave Peterson" wrote: 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 Cheri wrote: 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" wrote: 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 wrote: 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.? "Don Guillett" wrote: See if this idea helps Sub namesheets() For i = 1 To 18 Sheets("sheet" & i).Name = Cells(i, "a") Next i End Sub -- Don Guillett SalesAid Software "Cheri" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet tabs automatically change? | Excel Worksheet Functions | |||
Using a data validation list to look up a defined named range in another worksheet | Charts and Charting in Excel | |||
NAME TABS IN WORKSHEET FROM LIST | Excel Discussion (Misc queries) | |||
sort list of players by team from player list on separate sheet | Excel Worksheet Functions | |||
How can Excel create a list of tabs on a worksheet? | Excel Discussion (Misc queries) |