Is there a way to automatically name/rename a tab based on the cell contents
in a worksheet? Thanks.

Johnny,

Here is a start for you. It renames based on the values of column A.
So, if you start a blank workbook with 3 sheets and write in the cells
A1 = "pig"
A2 = "horse"
A3 = "cow"
then run this program, you will end up with 3 sheets called pig, horse and
cow.


Sub namesheets()

Dim x As Integer

For x = 1 To Sheets.Count
Sheets(x).Name = Cells(x, 1).Value
Next x

End Sub
--
Allllen


"Johnny" wrote:

Is there a way to automatically name/rename a tab based on the cell contents
in a worksheet? Thanks.

Thanks for your reply Alan. In my workbook, I have 4 workseets in which I
do not want to rename the tabs. Let me be a little more discriptive on what
I need. I want to be name each tab based on the cell contains in particular
that particular worksheet. For examples, if cell A1 in worksheet 1 is "Bob"
then name the tab Bob. Can you help? Thanks.

"Allllen" wrote:

Johnny,

Here is a start for you. It renames based on the values of column A.
So, if you start a blank workbook with 3 sheets and write in the cells
A1 = "pig"
A2 = "horse"
A3 = "cow"
then run this program, you will end up with 3 sheets called pig, horse and
cow.


Sub namesheets()

Dim x As Integer

For x = 1 To Sheets.Count
Sheets(x).Name = Cells(x, 1).Value
Next x

End Sub
--
Allllen


"Johnny" wrote:

Is there a way to automatically name/rename a tab based on the cell contents
in a worksheet? Thanks.

Lots of different ways such as
for each ws in worksheets
if ws.name<"Bill" and ws.name<"sam" then ws.name=range("a1")
next
--
Don Guillett
SalesAid Software

"Johnny" wrote in message
...
Thanks for your reply Alan. In my workbook, I have 4 workseets in which
I
do not want to rename the tabs. Let me be a little more discriptive on
what
I need. I want to be name each tab based on the cell contains in
particular
that particular worksheet. For examples, if cell A1 in worksheet 1 is
"Bob"
then name the tab Bob. Can you help? Thanks.

"Allllen" wrote:

Johnny,

Here is a start for you. It renames based on the values of column A.
So, if you start a blank workbook with 3 sheets and write in the cells
A1 = "pig"
A2 = "horse"
A3 = "cow"
then run this program, you will end up with 3 sheets called pig, horse
and
cow.


Sub namesheets()

Dim x As Integer

For x = 1 To Sheets.Count
Sheets(x).Name = Cells(x, 1).Value
Next x

End Sub
--
Allllen


"Johnny" wrote:

Is there a way to automatically name/rename a tab based on the cell
contents
in a worksheet? Thanks.

I meant to say that I did not want to rename all of the tabs in the workbook.
I do however wish to auto re-name only a select view base on the cells
contents in that tab.

"Johnny" wrote:

Thanks for your reply Alan. In my workbook, I have 4 workseets in which I
do not want to rename the tabs. Let me be a little more discriptive on what
I need. I want to be name each tab based on the cell contains in particular
that particular worksheet. For examples, if cell A1 in worksheet 1 is "Bob"
then name the tab Bob. Can you help? Thanks.

"Allllen" wrote:

Johnny,

Here is a start for you. It renames based on the values of column A.
So, if you start a blank workbook with 3 sheets and write in the cells
A1 = "pig"
A2 = "horse"
A3 = "cow"
then run this program, you will end up with 3 sheets called pig, horse and
cow.


Sub namesheets()

Dim x As Integer

For x = 1 To Sheets.Count
Sheets(x).Name = Cells(x, 1).Value
Next x

End Sub
--
Allllen


"Johnny" wrote:

Is there a way to automatically name/rename a tab based on the cell contents
in a worksheet? Thanks.

Johnny

You can paste this code into the sheet module of those you wish to rename
automatically.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Me.Name = Range("A1").Text
CleanUp:
Application.EnableEvents = True
End Sub

This is sheet event code.

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Change the value of A1 and sheet name will follow.


Gord Dibben MS Excel MVP


On Tue, 29 Aug 2006 05:28:01 -0700, Johnny
wrote:

I meant to say that I did not want to rename all of the tabs in the workbook.
I do however wish to auto re-name only a select view base on the cells
contents in that tab.

"Johnny" wrote:

Thanks for your reply Alan. In my workbook, I have 4 workseets in which I
do not want to rename the tabs. Let me be a little more discriptive on what
I need. I want to be name each tab based on the cell contains in particular
that particular worksheet. For examples, if cell A1 in worksheet 1 is "Bob"
then name the tab Bob. Can you help? Thanks.

"Allllen" wrote:

Johnny,

Here is a start for you. It renames based on the values of column A.
So, if you start a blank workbook with 3 sheets and write in the cells
A1 = "pig"
A2 = "horse"
A3 = "cow"
then run this program, you will end up with 3 sheets called pig, horse and
cow.


Sub namesheets()

Dim x As Integer

For x = 1 To Sheets.Count
Sheets(x).Name = Cells(x, 1).Value
Next x

End Sub
--
Allllen


"Johnny" wrote:

Is there a way to automatically name/rename a tab based on the cell contents
in a worksheet? Thanks.

Gord Dibben MS Excel MVP