Worksheet Names is Defined by Cell value on Sheet 1 (named Summary

[NeedToKnow]

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.

 

[Gary''s Student]

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

 

[Bernie Deitrick]

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

 

[NeedToKnow]

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]

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

 

[Bernie Deitrick]

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

 

[NeedToKnow]

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.

 

[NeedToKnow]

Bernie,
My apologies, it was my error.
It worked great.
Thank you so much.
Thank you all.