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.
|