Tab Naming Macro problem - help needed

[James]

Hi all,

I have a list of people names (approx 20) that I would like to use to name
separate tabs in a spreadsheet. For each person, I need two named tabs (one
has all the calculated data and the other has a graph of selected
information). I need to set up a macro that automatically takes the names
from the list and renames the tabs accordingly.

If I was to reorder the list or add new names, the macro needs to
automatically update itself and rename the associated tabs.

For example:

A1: Bob
A2: Joe
A3: Gary etc etc

Two tabs required for each (1st: same as A1, 2nd: A1 graph) and so on. If
A1 was to change to Jim and A4 was added for Bob, the tabs need to reflect
this change.

Hope this makes sense.

Can anyone help?

Thanks

James

 

[Mike H]

Hi,

I'm not sure I fully inderstand the bit aboutchanging the order of names
which makes things very difficult because in the renaming process it is
likely you would try and have 2 sheets with the same name which is illegal.
To get around this the macro forst renames the shet 1,2,3 etc and then
renames them using a list in column A

I've included no error trapping for not enough sheets for example



Sub sonic()
For x = 1 To ThisWorkbook.Worksheets.Count
Worksheets(x).Name = x
Next
y = 1
For x = 1 To ThisWorkbook.Worksheets.Count Step 2
Worksheets(x).Name = Sheets(1).Cells(y, 1).Value
Worksheets(x + 1).Name = Sheets(1).Cells(y, 1).Value & " Graph"
y = y + 1
Next
End Sub

Mike

 

[James]

Mike,

Probably just bad explaining on my part! I have changed how I might
approach this and need your advice on the following:

I have the following code to select sheets:

Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4", "Sheet 5",
"Sheet 6", _
"Sheet 7", "Sheet 8", "Sheet 9", "Sheet 10", "Sheet 11", "Sheet 12",
"Sheet 13", _
"Sheet 14", "Sheet 15", "Sheet 16", "Sheet 17", "Sheet 18", "Sheet
19", "Sheet 20", _
"Sheet 21")).Select
Sheets("Sheet 1").Activate

I have then modified another macro that was suggested by someone else in a
different thread (can't find it for reference), to:

Sub ...
dim wks as worksheet
for each wks in activewindow.selectedsheets
wks.name=wks.range("C4").value
next wks
End Sub

Where C4 is the person's name.

This seems to work for the first instance, but then the sheet references
don't update themselves.

I also have another tab for each person's which I need to rename aswell, but
not sure how!

Have you got any ideas as to where I can go from here?

Thanks for your help.