Sheet name = cell value
 

Hi everyone. Is there a way for vba to run through all sheets in a
workbook, and set each sheet name to the value in cell B1 of that
sheet? Thanks!

Sheet name = cell value
 

Steve, here is one way,

Sub Rename_All_Sheets()
'will rename all sheets in the workbook
'to the value in B1
Dim WS As Worksheet
For Each WS In Worksheets
WS.Name = WS.Range("B1").Value
Next
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

wrote in message
ups.com...
Hi everyone. Is there a way for vba to run through all sheets in a
workbook, and set each sheet name to the value in cell B1 of that
sheet? Thanks!

Sheet name = cell value
 

Per :
Hi everyone. Is there a way for vba to run through all sheets in a
workbook, and set each sheet name to the value in cell B1 of that
sheet? Thanks!

Yes, but be aware that Excel imposes some limitations on a sheet's name.

Something like 31 chars max, and not including certain characters.

Try setting a sheet's name to something that's 40 chars long and the resulting
Error$ that it throws will list the special characters.
--
PeteCresswell