MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Rename worksheets

Posted by Dave on January 17, 2002 10:48 AM

I want to create a worksheet containing pupils names and a summary of thier grades. I also want to create a separate worksheet for each pupil to enter more details. Is it possible to automatically create a new worksheet with the same name as the pupil as I add them to my list?

Posted by TomUrtis on January 17, 2002 11:10 AM

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

Posted by Russell Hauf on January 17, 2002 11:15 AM

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:


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,


Sub Worksheet_Change(ByVal Target As Range)

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
Application.ScreenUpdating = True

End Sub

Posted by Richard Winfield on January 17, 2002 2:30 PM

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,