I am trying to set up a workbook with 11 Sheets in it.

One of these is the control sheet.

I want to change the tab names of all the other sheets from the control
sheet by entering the names in cells in the cotrol sheet.

For example, lets say I want sheet 1 to be named 'James' and sheet 2 to be
named 'Fred' etc. I want to enter "James in Cell 'B3' and "Fred" in Cell
'C3' of the Control Sheet and keep going until all 10 have names. I then
want this data to somehow change all the tab names of the other 10 sheets.

I have copied the names to Cell A1 of each of the 10 sheets and tried the
following, but this only renames the active sheet.

Public Sub RenameSheet()
NewName = Range("A1").Value
ActiveSheet.Name = NewName
End Sub

Any suggestions

Thanks

Cameron,

provided your control sheet is the first sheet in your file you could use "

Sub ChangeNames()
For I = 1 To Worksheets.Count
Sheets(I).Name = Sheets(1).Range("A1").Offset(I - 1, 0)
Next
End Sub

If your control sheet is not the first change the 1 in Sheets(1).Range ....
to the number of the sheet which applies.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Cameron MacRae" wrote in message
...
I am trying to set up a workbook with 11 Sheets in it.

One of these is the control sheet.

I want to change the tab names of all the other sheets from the control
sheet by entering the names in cells in the cotrol sheet.

For example, lets say I want sheet 1 to be named 'James' and sheet 2 to be
named 'Fred' etc. I want to enter "James in Cell 'B3' and "Fred" in Cell
'C3' of the Control Sheet and keep going until all 10 have names. I then
want this data to somehow change all the tab names of the other 10 sheets.

I have copied the names to Cell A1 of each of the 10 sheets and tried the
following, but this only renames the active sheet.

Public Sub RenameSheet()
NewName = Range("A1").Value
ActiveSheet.Name = NewName
End Sub

Any suggestions

Thanks

This is my first reply to a post here, so forgive me if the formatting
isn't the usual.

Here's one example of what you can do. From what i know of VBA the
easiest way would be to use the "Sheet2.Name" to alter the sheets
tab-name.

Everytime you enter a new name into A1, B1 etc. the change refreshes
the tab names.

Not very elegant, but it works.



Private Sub Worksheet_Change(ByVal Target As Range)

Sheet2.Name = Sheets("Control").Range("A1").Value
Sheet3.Name = Sheets("Control").Range("B1").Value


End Sub


---
Message posted from http://www.ExcelForum.com/