Sheet name = cell value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

steve_andruszka@yahoo.com - 17 Jan 2007 15:41 GMT

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!

Reply to this Message

Paul B - 17 Jan 2007 15:50 GMT

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

Signature

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

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

Reply to this Message

(PeteCresswell) - 17 Jan 2007 17:58 GMT

Per steve_andruszka@yahoo.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!

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.

Signature

PeteCresswell

Reply to this Message