tab named after a cell contents

[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

 

[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

 

[smw226 via OfficeKB.com]

Hi Paul,

This will add a new tab for every value in a list starting in cell A1:

Sub createtabs()

Dim lastvalue As Integer
Dim currentrow As Integer
Dim mastersheet As String
Dim tabname As String

Range("A65536").Select
Selection.End(xlUp).Select
lastvalue = ActiveCell.Row
lastvalue = lastvalue + 1
currentrow = 1
mastersheet = ActiveSheet.Name

Do While lastvalue <> currentrow
Range("A" & currentrow).Select
tabname = ActiveCell.Value
Sheets.Add
ActiveSheet.Name = tabname
Sheets(mastersheet).Select
currentrow = currentrow + 1
Loop

MsgBox ("Complete")

End Sub

Please see the advice above for how to get it into VBA

Thanks,

Simon

 

[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.

 

[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.

 

[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

 

[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

 

[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

 

[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

 

[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

 

[Don Guillett]

Huh?

 

[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

 

[Jack Sons]

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

Jack.

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

 

[Don Guillett]

glad to help. Most lists would have had a header.