Workshet tab names from Cell names

mnmhenry

Board Regular
Joined
Mar 28, 2002
Messages
169
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
Mar 28, 2002
Messages
169
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
Mar 28, 2002
Messages
169
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
Mar 28, 2002
Messages
169
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

Board Regular
Joined
Feb 15, 2002
Messages
65
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
 

Forum statistics

Threads
1,181,531
Messages
5,930,432
Members
436,738
Latest member
JFry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top