Rename worksheets

Yes this is possible, just invoke this macro or its code, assuming your list in in Sheet1 (note VB sheet code object reference number, not necessarily "Sheet1" as you see it on your sheet tab); and assuming your pupil list is in column A.

Sub WkshtAdd_Name()
Worksheets.Add.Name = Sheet1.Range("A65536").End(xlUp).Value
End Sub

Tom Urtis

That's a fun one. Ok, in my workbook, the summary sheet is called "Summary". In Column A, I have my list of pupils. In A1 I have a header named (appropriately), "Pupils". The pupils start in A2. Now, in another cell in my workbook (not in column A, but anywhere else you want), I have typed the formula:

=COUNTA(A:A)

and I've named this cell "PupilCount" (inappropriately, because it's actually the number of pupils PLUS ONE - but that's what we want).

Ok, now go to the Visual Basic Editor and in the Properties window you should see your workbook - something like:

VBAProject (MyWorkbookName.xls)

and underneath that, you will see a folder named Microsoft Excel Objects. If it's not already open, do so. Then double-click on your Summary sheet. This should open a new window. Paste the following code (modifying the name of your summary sheet if necessary), and you should be ready to go. A few caveats: 1) make sure that you have no other sheets besides the summary sheet and one for each student in your list. 2) if you do have pupil sheets already, make sure that there is one sheet for each student on your list.

I hope this helps,

Russell

Private

If Target.Column = 1 Then
If Range("PupilCount").Value = Worksheets.Count + 1 Then
Application.ScreenUpdating = False
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Left(Target.Text, 31)
End If
End If
Worksheets("Summary").Select
Application.ScreenUpdating = True

End Sub

The following macro assumes that the pupil's names are going to be inserted in the range of A2 to A50. If more than 49 pupils or if you wish to enter the names for example in the B column then change the range accordingly. Each time you enter a name and either hit enter or move to another cell this will automatically create a new sheet in the same workbook with the pupils name as the title then return you back to sheet1 to continue entering names. To install this open the workbook and right click on the sheet1 (or whatever name the sheet is where you are entering the pupils names) tab, choose "View Code"
and paste the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.ScreenUpdating = False
Dim rngCell As Range, rngIntersect As Range
On Error Resume Next

Set rngIntersect = Intersect(Target, Range("a2:a50")) 'change this if neccessary to allow for more cells
If rngIntersect Is Nothing Then
Exit Sub
End If

For Each rngCell In rngIntersect.Cells
If rngCell.Value <> "" Then
Worksheets.Add.Name = Sheet1.Range("A65536").End(xlUp).Value
Sheets("Sheet1").Select 'change "sheet1" to the actual name of your pupil list sheet if necessary
End If
Next rngCell
Application.ScreenUpdating = True
End Sub

Hope this helps,

Rick