change name of sheet from list

[BNT1 via OfficeKB.com]

Hi

I have a list on sheet "summary" range A5:A55

Is there a code I can cut and paste which will rename the other sheets in the
workbook from the summary sheet range A5:A55

thanks in advance


brian

 

[Mike H]

One way. This takes the values from the range A5 down and uses those values
to rename all the worksheets in the workbook. If there is a blank cell
before all the sheets are renamed then the sub ends. Right click the sheet
tab with your list in, view code and paste this in:-

Sub renameit()
x = 5
On Error GoTo 100
For Each Worksheet In Worksheets
Worksheet.Name = Cells(x, 1).Value
x = x + 1
Next
100
End Sub

Mike

 

[BNT1 via OfficeKB.com]

Thanks mike for the speedy reply,
work perfect

regards

One way. This takes the values from the range A5 down and uses those values
to rename all the worksheets in the workbook. If there is a blank cell
before all the sheets are renamed then the sub ends. Right click the sheet
tab with your list in, view code and paste this in:-

Sub renameit()
x = 5
On Error GoTo 100
For Each Worksheet In Worksheets
Worksheet.Name = Cells(x, 1).Value
x = x + 1
Next
100
End Sub

Mike

Hi

[quoted text clipped - 6 lines]

brian

 

[BNT1 via OfficeKB.com]

Thanks mike for the speedy reply,
work perfect

regards

One way. This takes the values from the range A5 down and uses those values
to rename all the worksheets in the workbook. If there is a blank cell
before all the sheets are renamed then the sub ends. Right click the sheet
tab with your list in, view code and paste this in:-

Sub renameit()
x = 5
On Error GoTo 100
For Each Worksheet In Worksheets
Worksheet.Name = Cells(x, 1).Value
x = x + 1
Next
100
End Sub

Mike

Hi

[quoted text clipped - 6 lines]

brian

 

[BNT1 via OfficeKB.com]

Thanks mike for the speedy reply,
work perfect

regards

One way. This takes the values from the range A5 down and uses those values
to rename all the worksheets in the workbook. If there is a blank cell
before all the sheets are renamed then the sub ends. Right click the sheet
tab with your list in, view code and paste this in:-

Sub renameit()
x = 5
On Error GoTo 100
For Each Worksheet In Worksheets
Worksheet.Name = Cells(x, 1).Value
x = x + 1
Next
100
End Sub

Mike

Hi

[quoted text clipped - 6 lines]

brian

 

[BNT1 via OfficeKB.com]

Hi

One last question
I have got dates in the list-- when tested with numbers it worked ok, but now
come to use with dates, does not run
have tried different formats

any idea's

regards

Thanks mike for the speedy reply,
work perfect

regards

One way. This takes the values from the range A5 down and uses those values
to rename all the worksheets in the workbook. If there is a blank cell

[quoted text clipped - 18 lines]

 

[Don Guillett]

Try this for your dates in range("a2:a??")

One way.
Sub namesheets()
For i = 5 To Cells(Rows.Count, "a").End(xlUp).Row
Sheets(i-3).Name = Format(Cells(i, "a"), "yyyy-mm-dd")
Next i
End Sub