Workshet tab names from Cell names

mnmhenry

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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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
167
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
167

ADVERTISEMENT

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
167
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,144,734
Messages
5,725,983
Members
422,652
Latest member
Elnene1

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