change name of sheet from list
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200710/1

change name of sheet from list
 

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" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200710/1

change name of sheet from list
 

Thanks mike for the speedy reply,
work perfect

regards

Mike H wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200710/1

change name of sheet from list
 

Thanks mike for the speedy reply,
work perfect

regards

Mike H wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200710/1

change name of sheet from list
 

Thanks mike for the speedy reply,
work perfect

regards

Mike H wrote:
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

--
Message posted via http://www.officekb.com

change name of sheet from list
 

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

BNT1 wrote:
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]

brian

--
Message posted via http://www.officekb.com

change name of sheet from list
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:7953d0b190ed5@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200710/1