Workshet tab names from Cell names

[mnmhenry]

Hello again all. I am trusting you can help me again.

I need my Worksheet Tab names to equal my cell info.

Ie A1 Bhp Billiton
A2 Arc Smorgan
...and thus I want my Worksheet tab to be
(1st) Bhp Billiton (2nd) Arc Smorgan

I will have about 20 sheet tabs in total.

Thanks again
Mark

 

[PaddyD]

Try this:

<pre>

Sub tabname()
Dim ws As Worksheet

For Each ws In Worksheets
ws.Activate
ws.Name = Range("$D$5").Value
Next
End Sub


</pre>

Change the $d$5 reference to suit.

Paddy

 

[mnmhenry]

Ok. I think I need to elaborate. What I am trying to do is I have share prices of say 20 companys on sheet 1. I want to graph the individual companys onto their own sheet.

 

[PaddyD]

Just a little different from your original question, which didn't mention graphs at all!!


Can you elaborate just a little more! May be give a detailed example of what data you've got, how it's set up & what result you'd expect...

Paddy

 

[mnmhenry]

I didnt mention the graphs because They really arnt relevant at this stage. In Cells A1 to A20 I will have company names. I wish to run a macro that will give me these 20 names on the sheet tabs... Ie Change 3 or 4 company names, then run the macro and sheet names change to suit.

Everything else like graphs and data, I could handle myself.

Thanks for your reply.

 

[PaddyD]

Will the macro have to create the sheets as well or simply remane existing sheets? Might there already be a sheet with one of the names in the list (need to know this for error checking)...Do you what the default sheet1, sheet2 etc left...

Please describe the outcome you're looking for.

Paddy

 

[mnmhenry]

There will be 20 existing sheet allready there so it will have to rename them from defaut (sheet 1 sheet 2) at first and then rename them again if a company is changed.

 

[Carl B]

Sub tabname()
Dim ws As Worksheet
Dim MyRow As Integer

MyRow = 1
For Each ws In Worksheets
ws.Activate
ws.Name = Cells(MyRow,1).Value
MyRow = MyRow + 1
Next
End Sub