Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Names is Defined by Cell value on Sheet 1 (named Summary
Is there a way to have a certain worksheet assume the text in a cell:
sheet 2 renaming itself to whatever is in cell a1 of Summary (formerly sheet1) sheet 3 renaming itself to whatever is in cell a5 of Summary (formerly sheet1) sheet 4 renaming itself to whatever is in cell a9 of Summary (formerly sheet1) and so on...worksheets count will vary depending on projects ALSO, automatically updating any reference to the renamed sheet throughout the workbook. Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Names is Defined by Cell value on Sheet 1 (named Summary
Say we have seven sheets and in the first sheet (does not matter what the
name is) we have: alpha beta gamma delta zeta eta in cells A1 thru A6 Run this macro to re-name the following 6 sheets: Sub needto() Sheets(1).Activate For i = 1 To 6 Sheets(i + 1).Name = Cells(i, 1).Value Next End Sub -- Gary''s Student - gsnu200824 "NeedToKnow" wrote: Is there a way to have a certain worksheet assume the text in a cell: sheet 2 renaming itself to whatever is in cell a1 of Summary (formerly sheet1) sheet 3 renaming itself to whatever is in cell a5 of Summary (formerly sheet1) sheet 4 renaming itself to whatever is in cell a9 of Summary (formerly sheet1) and so on...worksheets count will vary depending on projects ALSO, automatically updating any reference to the renamed sheet throughout the workbook. Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Names is Defined by Cell value on Sheet 1 (named Summary
To use the cell spacing of the OP's request:
Sub ChangeSheetNames() Dim i As Integer For i = 2 To Worksheets.Count WorkSheets(i).Name = Sheets("Summary").Cells(i*4-7, 1).Value Next i End Sub And if Sheet Summary can be moved to any position: Sub ChangeSheetNames2() Dim i As Integer Dim j As Integer j = 0 For i = 1 To Worksheets.Count If WorkSheets(i).Name < "Summary" Then j = j + 1 WorkSheets(i).Name = Sheets("Summary").Cells(j*4-3, 1).Value End If Next i End Sub HTH, Bernie MS Excel MVP "Gary''s Student" wrote in message ... Say we have seven sheets and in the first sheet (does not matter what the name is) we have: alpha beta gamma delta zeta eta in cells A1 thru A6 Run this macro to re-name the following 6 sheets: Sub needto() Sheets(1).Activate For i = 1 To 6 Sheets(i + 1).Name = Cells(i, 1).Value Next End Sub -- Gary''s Student - gsnu200824 "NeedToKnow" wrote: Is there a way to have a certain worksheet assume the text in a cell: sheet 2 renaming itself to whatever is in cell a1 of Summary (formerly sheet1) sheet 3 renaming itself to whatever is in cell a5 of Summary (formerly sheet1) sheet 4 renaming itself to whatever is in cell a9 of Summary (formerly sheet1) and so on...worksheets count will vary depending on projects ALSO, automatically updating any reference to the renamed sheet throughout the workbook. Thanks in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Names is Defined by Cell value on Sheet 1 (named Sum
Thank you guys, I will try them both tonight.
However, if you could please look into this bit of change on the cell locations from Sheet1: Instead of A1, A5, A9, A13 and so on as previously described, it's now C2, F2, I2, L2 and so on. Thanks again. "Bernie Deitrick" wrote: To use the cell spacing of the OP's request: Sub ChangeSheetNames() Dim i As Integer For i = 2 To Worksheets.Count WorkSheets(i).Name = Sheets("Summary").Cells(i*4-7, 1).Value Next i End Sub And if Sheet Summary can be moved to any position: Sub ChangeSheetNames2() Dim i As Integer Dim j As Integer j = 0 For i = 1 To Worksheets.Count If WorkSheets(i).Name < "Summary" Then j = j + 1 WorkSheets(i).Name = Sheets("Summary").Cells(j*4-3, 1).Value End If Next i End Sub HTH, Bernie MS Excel MVP "Gary''s Student" wrote in message ... Say we have seven sheets and in the first sheet (does not matter what the name is) we have: alpha beta gamma delta zeta eta in cells A1 thru A6 Run this macro to re-name the following 6 sheets: Sub needto() Sheets(1).Activate For i = 1 To 6 Sheets(i + 1).Name = Cells(i, 1).Value Next End Sub -- Gary''s Student - gsnu200824 "NeedToKnow" wrote: Is there a way to have a certain worksheet assume the text in a cell: sheet 2 renaming itself to whatever is in cell a1 of Summary (formerly sheet1) sheet 3 renaming itself to whatever is in cell a5 of Summary (formerly sheet1) sheet 4 renaming itself to whatever is in cell a9 of Summary (formerly sheet1) and so on...worksheets count will vary depending on projects ALSO, automatically updating any reference to the renamed sheet throughout the workbook. Thanks in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Names is Defined by Cell value on Sheet 1 (named Sum
Sub ChangeSheetNames2()
Dim i As Integer For i = 2 To Worksheets.Count WorkSheets(i).Name = Sheets("Summary").Cells(2,i*3-3, 1).Value Next i End Sub HTH, Bernie MS Excel MVP "NeedToKnow" wrote in message ... Thank you guys, I will try them both tonight. However, if you could please look into this bit of change on the cell locations from Sheet1: Instead of A1, A5, A9, A13 and so on as previously described, it's now C2, F2, I2, L2 and so on. Thanks again. "Bernie Deitrick" wrote: To use the cell spacing of the OP's request: Sub ChangeSheetNames() Dim i As Integer For i = 2 To Worksheets.Count WorkSheets(i).Name = Sheets("Summary").Cells(i*4-7, 1).Value Next i End Sub And if Sheet Summary can be moved to any position: Sub ChangeSheetNames2() Dim i As Integer Dim j As Integer j = 0 For i = 1 To Worksheets.Count If WorkSheets(i).Name < "Summary" Then j = j + 1 WorkSheets(i).Name = Sheets("Summary").Cells(j*4-3, 1).Value End If Next i End Sub HTH, Bernie MS Excel MVP "Gary''s Student" wrote in message ... Say we have seven sheets and in the first sheet (does not matter what the name is) we have: alpha beta gamma delta zeta eta in cells A1 thru A6 Run this macro to re-name the following 6 sheets: Sub needto() Sheets(1).Activate For i = 1 To 6 Sheets(i + 1).Name = Cells(i, 1).Value Next End Sub -- Gary''s Student - gsnu200824 "NeedToKnow" wrote: Is there a way to have a certain worksheet assume the text in a cell: sheet 2 renaming itself to whatever is in cell a1 of Summary (formerly sheet1) sheet 3 renaming itself to whatever is in cell a5 of Summary (formerly sheet1) sheet 4 renaming itself to whatever is in cell a9 of Summary (formerly sheet1) and so on...worksheets count will vary depending on projects ALSO, automatically updating any reference to the renamed sheet throughout the workbook. Thanks in advance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Names is Defined by Cell value on Sheet 1 (named Sum
Oops, forgot to delete the last , 1 from the cells....
WorkSheets(i).Name = Sheets("Summary").Cells(2,i*3-3).Value HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Sub ChangeSheetNames2() Dim i As Integer For i = 2 To Worksheets.Count WorkSheets(i).Name = Sheets("Summary").Cells(2,i*3-3, 1).Value Next i End Sub HTH, Bernie MS Excel MVP "NeedToKnow" wrote in message ... Thank you guys, I will try them both tonight. However, if you could please look into this bit of change on the cell locations from Sheet1: Instead of A1, A5, A9, A13 and so on as previously described, it's now C2, F2, I2, L2 and so on. Thanks again. "Bernie Deitrick" wrote: To use the cell spacing of the OP's request: Sub ChangeSheetNames() Dim i As Integer For i = 2 To Worksheets.Count WorkSheets(i).Name = Sheets("Summary").Cells(i*4-7, 1).Value Next i End Sub And if Sheet Summary can be moved to any position: Sub ChangeSheetNames2() Dim i As Integer Dim j As Integer j = 0 For i = 1 To Worksheets.Count If WorkSheets(i).Name < "Summary" Then j = j + 1 WorkSheets(i).Name = Sheets("Summary").Cells(j*4-3, 1).Value End If Next i End Sub HTH, Bernie MS Excel MVP "Gary''s Student" wrote in message ... Say we have seven sheets and in the first sheet (does not matter what the name is) we have: alpha beta gamma delta zeta eta in cells A1 thru A6 Run this macro to re-name the following 6 sheets: Sub needto() Sheets(1).Activate For i = 1 To 6 Sheets(i + 1).Name = Cells(i, 1).Value Next End Sub -- Gary''s Student - gsnu200824 "NeedToKnow" wrote: Is there a way to have a certain worksheet assume the text in a cell: sheet 2 renaming itself to whatever is in cell a1 of Summary (formerly sheet1) sheet 3 renaming itself to whatever is in cell a5 of Summary (formerly sheet1) sheet 4 renaming itself to whatever is in cell a9 of Summary (formerly sheet1) and so on...worksheets count will vary depending on projects ALSO, automatically updating any reference to the renamed sheet throughout the workbook. Thanks in advance. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Names is Defined by Cell value on Sheet 1 (named Sum
Bernie,
I pasted the macro in Sheet1 with the correction you mentioned and it compiled properly. I have 6 sheets (Sheet1 through Sheet6). However, even after I've saved the spreadsheet and opened it up again, the results we're trying to accomplish did not materialize. I changed the tab name of Sheet1 to "Summary" also. Could you please let me know where I missed out on the macro? Should there be some kind of increment on "i"? Thanks. "Bernie Deitrick" wrote: Oops, forgot to delete the last , 1 from the cells.... WorkSheets(i).Name = Sheets("Summary").Cells(2,i*3-3).Value HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Sub ChangeSheetNames2() Dim i As Integer For i = 2 To Worksheets.Count WorkSheets(i).Name = Sheets("Summary").Cells(2,i*3-3, 1).Value Next i End Sub HTH, Bernie MS Excel MVP "NeedToKnow" wrote in message ... Thank you guys, I will try them both tonight. However, if you could please look into this bit of change on the cell locations from Sheet1: Instead of A1, A5, A9, A13 and so on as previously described, it's now C2, F2, I2, L2 and so on. Thanks again. "Bernie Deitrick" wrote: To use the cell spacing of the OP's request: Sub ChangeSheetNames() Dim i As Integer For i = 2 To Worksheets.Count WorkSheets(i).Name = Sheets("Summary").Cells(i*4-7, 1).Value Next i End Sub And if Sheet Summary can be moved to any position: Sub ChangeSheetNames2() Dim i As Integer Dim j As Integer j = 0 For i = 1 To Worksheets.Count If WorkSheets(i).Name < "Summary" Then j = j + 1 WorkSheets(i).Name = Sheets("Summary").Cells(j*4-3, 1).Value End If Next i End Sub HTH, Bernie MS Excel MVP "Gary''s Student" wrote in message ... Say we have seven sheets and in the first sheet (does not matter what the name is) we have: alpha beta gamma delta zeta eta in cells A1 thru A6 Run this macro to re-name the following 6 sheets: Sub needto() Sheets(1).Activate For i = 1 To 6 Sheets(i + 1).Name = Cells(i, 1).Value Next End Sub -- Gary''s Student - gsnu200824 "NeedToKnow" wrote: Is there a way to have a certain worksheet assume the text in a cell: sheet 2 renaming itself to whatever is in cell a1 of Summary (formerly sheet1) sheet 3 renaming itself to whatever is in cell a5 of Summary (formerly sheet1) sheet 4 renaming itself to whatever is in cell a9 of Summary (formerly sheet1) and so on...worksheets count will vary depending on projects ALSO, automatically updating any reference to the renamed sheet throughout the workbook. Thanks in advance. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Names is Defined by Cell value on Sheet 1 (named Sum
Bernie,
My apologies, it was my error. It worked great. Thank you so much. Thank you all. "NeedToKnow" wrote: Bernie, I pasted the macro in Sheet1 with the correction you mentioned and it compiled properly. I have 6 sheets (Sheet1 through Sheet6). However, even after I've saved the spreadsheet and opened it up again, the results we're trying to accomplish did not materialize. I changed the tab name of Sheet1 to "Summary" also. Could you please let me know where I missed out on the macro? Should there be some kind of increment on "i"? Thanks. "Bernie Deitrick" wrote: Oops, forgot to delete the last , 1 from the cells.... WorkSheets(i).Name = Sheets("Summary").Cells(2,i*3-3).Value HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Sub ChangeSheetNames2() Dim i As Integer For i = 2 To Worksheets.Count WorkSheets(i).Name = Sheets("Summary").Cells(2,i*3-3, 1).Value Next i End Sub HTH, Bernie MS Excel MVP "NeedToKnow" wrote in message ... Thank you guys, I will try them both tonight. However, if you could please look into this bit of change on the cell locations from Sheet1: Instead of A1, A5, A9, A13 and so on as previously described, it's now C2, F2, I2, L2 and so on. Thanks again. "Bernie Deitrick" wrote: To use the cell spacing of the OP's request: Sub ChangeSheetNames() Dim i As Integer For i = 2 To Worksheets.Count WorkSheets(i).Name = Sheets("Summary").Cells(i*4-7, 1).Value Next i End Sub And if Sheet Summary can be moved to any position: Sub ChangeSheetNames2() Dim i As Integer Dim j As Integer j = 0 For i = 1 To Worksheets.Count If WorkSheets(i).Name < "Summary" Then j = j + 1 WorkSheets(i).Name = Sheets("Summary").Cells(j*4-3, 1).Value End If Next i End Sub HTH, Bernie MS Excel MVP "Gary''s Student" wrote in message ... Say we have seven sheets and in the first sheet (does not matter what the name is) we have: alpha beta gamma delta zeta eta in cells A1 thru A6 Run this macro to re-name the following 6 sheets: Sub needto() Sheets(1).Activate For i = 1 To 6 Sheets(i + 1).Name = Cells(i, 1).Value Next End Sub -- Gary''s Student - gsnu200824 "NeedToKnow" wrote: Is there a way to have a certain worksheet assume the text in a cell: sheet 2 renaming itself to whatever is in cell a1 of Summary (formerly sheet1) sheet 3 renaming itself to whatever is in cell a5 of Summary (formerly sheet1) sheet 4 renaming itself to whatever is in cell a9 of Summary (formerly sheet1) and so on...worksheets count will vary depending on projects ALSO, automatically updating any reference to the renamed sheet throughout the workbook. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summary of Worksheet names | Excel Discussion (Misc queries) | |||
Summary sheet from tab names | Excel Discussion (Misc queries) | |||
Displaying information (contained in defined names) on a summary sheet, in different row numbers? | Excel Discussion (Misc queries) | |||
Using a data validation list to look up a defined named range in another worksheet | Charts and Charting in Excel | |||
Using non defined names from another sheet | Excel Worksheet Functions |