Worksheet tabs named according to Index worksheet cell values.

[Terminal]

Hi All

I have a workbook used for tracking pupil progress, each tab represents a pupil but they are named generically Pupil1 Pupil2 etc.
The workbook has an index worksheet which has the students names in a column and hyperlinks next to each name linking to a worksheet. The teacher has asked if she can have the worksheets tabs named after each student. As each worksheet is protected she cannot do this manually and this would be a long task for 120 pupils anyway. Would this be possible via VBA\Macro so each year only the Index worksheet would need changing and the tabs would be updated accordingly? Please be gentle with me as it's been 10yrs+ since I've done any vba and that was in Access not Excel

Any help would be much appreciated....

 

[Fluff]

Ok, thanks for that.
There is something odd about the cells A3:A5, but not sure what.
Remove the hyperlinks in each of those cells & rebuild them, then try

Sub Name_Pupils()
   Dim Cl As Range, Rng As Range
   Dim Ws As Worksheet
   
   If ActiveSheet.ProtectContents = True Then Exit Sub

   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Evaluate("isref(" & Cl.Hyperlinks(1).SubAddress & ")") Then
         Set Rng = Evaluate(Cl.Hyperlinks(1).SubAddress)
         Set Ws = Sheets(Rng.Parent.Name)
         Ws.Name = Cl.Offset(, 1).Value
         With Cl.Hyperlinks(1)
            .SubAddress = ActiveSheet.Name & "!A1"
            .TextToDisplay = Cl.Offset(, 1).Value
         End With
      End If
   Next Cl
End Sub

 

[Terminal]

Ok, thanks for that.
There is something odd about the cells A3:A5, but not sure what.
Remove the hyperlinks in each of those cells & rebuild them, then try

Sub Name_Pupils()
   Dim Cl As Range, Rng As Range
   Dim Ws As Worksheet
   
   If ActiveSheet.ProtectContents = True Then Exit Sub

   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Evaluate("isref(" & Cl.Hyperlinks(1).SubAddress & ")") Then
         Set Rng = Evaluate(Cl.Hyperlinks(1).SubAddress)
         Set Ws = Sheets(Rng.Parent.Name)
         Ws.Name = Cl.Offset(, 1).Value
         With Cl.Hyperlinks(1)
            .SubAddress = ActiveSheet.Name & "!A1"
            .TextToDisplay = Cl.Offset(, 1).Value
         End With
      End If
   Next Cl
End Sub

Hi Fluff

Thank you so much for the code!!!

I tried the Macro on a workbook with the first 5 hyperlinks re-created...
The renaming of the tabs worked perfectly, however it broke all the hyperlinks in the Index worksheet?
Not sure what it did as they still displayed as hyperlinks but did not link to anything?

Any Ideas??

 

[Fluff]

Any Ideas??

Yes, I'm an idiot
Forgot to change this line as shown, so everything was linked to A1 on the Index sheet.

      With Cl.Hyperlinks(1)
         .SubAddress = [COLOR=#ff0000]s.Name[/COLOR] & "!A1"
         .TextToDisplay = Cl.Offset(, 1).Value
      End With

 

[Terminal]

Yes, I'm an idiot
Forgot to change this line as shown, so everything was linked to A1 on the Index sheet.

      With Cl.Hyperlinks(1)
         .SubAddress = [COLOR=#ff0000]s.Name[/COLOR] & "!A1"
         .TextToDisplay = Cl.Offset(, 1).Value
      End With

Hi Fluff

No... you certainly are not an idiot, wouldn't have got this far without your valued assistance. (Ws.Name & "!A1")
That has sorted it... Tabs are renaming and Hyperlinks work as well. The only thing I have found during my testing is that I may need to create a reset Macro to get the index worksheet ready for a fresh intake of children. This is because a new class of pupils may contain pupils with the same name as the previous class, this of course breaks the Name_Pupils macro...
I think I can cobble somthing together based on my old knowledge of VB, if I get stuck maybe I could post another comment here!
Thanks again for all your assistance fluff, very much appreciated...

 

[Fluff]

Glad to help & thanks for the feedback