tab named after a cell contents

P

Paul

Hi all,
I'm a teacher with a class of 30+ children. I want to keep an individual
worksheet for each child but don't want to have to type in their names to
each tab. I would like to either 1) have a simple class list on the first
tab and then each subsequent tab named from that list, or 2) from a
particular cell on each individual worksheet. I'm an average user of excel
2003 but have no knowledge of VBA.
Hope you can help
 
P

Paul B

Paul, here is one way,

Sub Rename_All_Sheets()
'will rename all sheets in the workbook to the value in A1
Dim WS As Worksheet
For Each WS In Worksheets
WS.Name = WS.Range("A1").Value
Next
End Sub

To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is at the top-left of the spreadsheet this will
open the VBA editor, in Project Explorer click on your workbook name, if you
don't see it press CTRL + r to open the Project Explorer, then go to insert,
module, and paste the code in the window that opens on the right hand side,
press Alt and Q to close this window and go back to your workbook and press
alt and F8, this will bring up a box to pick the Macro from, click on the
Macro name to run it. If you are using excel 2000 or newer you may have to
change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium

And if you are new to macros you may also what to have a look here on
getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
P

Paul

Paul,
I can only thank you for your prompt help. I have asigned a button to run
the macro so can change the names quickly and easily.
Once again thanks and I will check the web page you recommended.
 
D

Don Guillett

With that script you would have had to first create each worksheet and put
the name in cell a1. Somewhat easier to do it from a list in the first ws.
 
P

Paul B

Your welcome, thanks for the feedback, and if you need it from a list you
have that also
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
J

Jack Sons

Don,

The sheets with their tabs appear back to front compared to the list; jan
feb mar results in mar being the left most tab and jan the right most. How
should the code be revised to overcome this effect?

Jack Sons
The Netherlands
 
P

Paul

Hi,
Now I can see why people go on about newsgroups, some really useful help.
Both Don's and Simon's help has been thought provoking for other workbooks.
I already had set up the sheets to read the kids name from a list on the
first tab so altering the macro to read from that cell was easy. Next time I
create a multiple tabbed workbook based on kids names I now know what to do
first.
Thanks to you all.
Paul
 
D

Don Guillett

Sub namesheetsbottomup()
With Sheets("sheet1")'where list is in col A
For i = .Cells(.Rows.Count, "a").End(xlUp).Row To 2 Step -1
Sheets.Add.Name = .Cells(i, "a")
Next
End With
End Sub
 
J

Jack Sons

Don,

The first element of the list has to be not in row 1. If one forgets that,
there will be no sheet with the first element as its tab and the sheet
holding the list will disappear. Can you revise the code so that the list
may start at row 1?

Jack.

The resuklt of your code is
 
P

Paul B

Jack, don't know about the sheet disappearing, don't think Don knows either,
but to make the list start in row one just change
To 2 Step -1 to To 1 Step -1

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
J

Jack Sons

Yes! That's it. Thanks Paul and Don.

Jack.

Paul B said:
Jack, don't know about the sheet disappearing, don't think Don knows
either,
but to make the list start in row one just change
To 2 Step -1 to To 1 Step -1

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
Click to expand...
 
Top