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

 

[mumps]

Does each student sheet contain the student's name anywhere on it? If so, in which cell in each sheet is the named located?

 

[Terminal]

Does each student sheet contain the student's name anywhere on it? If so, in which cell in each sheet is the named located?

Hi mumps

No the pupil worksheets are totally generic, it was designed that way for GDPR, the pupils names in the Index worksheet are actually hidden\unhidden with two macro's behind buttons which do not work until the Index worksheet is unlocked. However for some reason the Teacher finds this confusing and wants the pupils names on the actual tabs for each pupil. I have voiced my disaproval as these worksheets contain child development questions and answer which will be directly associated with a pupil using her method. However i'm just the IT guy so hey what do I know
The current Index worksheet has a hyperlink in B3 pointing to worksheet pupil01 a hyperlink in B4 pointing to worksheet pupil02 etc. etc. and in C3 is a students name and in C4 is a students name etc etc. I guess I could add an addtional column with the current worksheet tab name in it next to each pupil so B3=Hyperlink to Worksheet Pupil01 C3=Pupil Name D3=Pupil01 if that would help associate the two values....

 

[mumps]

Does the link in B3 look like this: pupil01!A1 and the link in B4 like this: pupil02!A1 ?

 

[Terminal]

Does the link in B3 look like this: pupil01!A1 and the link in B4 like this: pupil02!A1 ?

Hi mumps

Yes that's exactly how the links work... each cell in column B contains text 'Pupil01' which is a simple hyperlink within the workbook to the same named worksheet.

 

[Fluff]

How about

Sub Pupils()
   Dim Cl As Range
   For Each Cl In Range("B3", Range("B" & Rows.Count).End(xlUp))
      Cl.Hyperlinks(1).Follow
      ActiveSheet.Name = Cl.Offset(, 1).Value
      With Cl.Hyperlinks(1)
         .SubAddress = ActiveSheet.Name & "!A1"
        [COLOR=#0000ff] .TextToDisplay = Cl.Offset(, 1).Value[/COLOR]
      End With
   Next Cl
End Sub

If you don't want to change the display name of the hyperlink, remove the line in blue.
The index sheet needs to be active when you run this.

 

[Terminal]

How about

Sub Pupils()
   Dim Cl As Range
   For Each Cl In Range("B3", Range("B" & Rows.Count).End(xlUp))
      Cl.Hyperlinks(1).Follow
      ActiveSheet.Name = Cl.Offset(, 1).Value
      With Cl.Hyperlinks(1)
         .SubAddress = ActiveSheet.Name & "!A1"
        [COLOR=#0000ff] .TextToDisplay = Cl.Offset(, 1).Value[/COLOR]
      End With
   Next Cl
End Sub

If you don't want to change the display name of the hyperlink, remove the line in blue.
The index sheet needs to be active when you run this.

Hi Fluff

Thank you so much for the code... I have applied it behind a 3rd button on the Index and for the most part it seems to work as expected. The only problem I found was the first few names did not apply correctly. So the first pupil name applied to the correct worksheet, the 2nd, 3rd, & 4th pupil names didn't apply at all then the 5th pupil name was applied to the index worksheet. There after it worked perfectly... it may be that the teacher had managed to break the hyperlinks as she has the password to unprotect that worksheet so she can run the hide\unhide macros and edit the names. When I get a chance I will take a look at the hyperlinks to see if that was the issue. I will post an update once I have taken a look... thanks again for your help.

 

[mumps]

With the "Index" being the active sheet, try:

Sub RenameTabs()
    Application.ScreenUpdating = False
    Dim hl As Hyperlink, x As Long
    Dim bottomB As Long
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    For x = 1 To bottomB - 2
        Set hl = ActiveSheet.Hyperlinks(x)
        Set r = Application.Evaluate(hl.SubAddress)
        Sheets(r.Parent.Name).Name = Cells(x + 2, 3)
    Next x
    Application.ScreenUpdating = True
End Sub

 

[Terminal]

With the "Index" being the active sheet, try:

Sub RenameTabs()
    Application.ScreenUpdating = False
    Dim hl As Hyperlink, x As Long
    Dim bottomB As Long
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    For x = 1 To bottomB - 2
        Set hl = ActiveSheet.Hyperlinks(x)
        Set r = Application.Evaluate(hl.SubAddress)
        Sheets(r.Parent.Name).Name = Cells(x + 2, 3)
    Next x
    Application.ScreenUpdating = True
End Sub

Hi mumps

Thank you for the code... sorry I didn't get back to you sooner I was AFK over the weekend.
I tried the above behind a third button but I am getting a Run-time error '1004' @ Sheets(r.Parent.Name).Name = Cells(x + 2, 3)
Not sure why, as I mentioned in my original post my VBA is very rusty I mainly work in c# these days... Any idea's??

Many thanks.

 

[Terminal]

Hi Fluff

I checked the worksheet and the hyperlinks all seem to be OK... so I am not sure why I am getting the odd behaviour on the first few tabs?
Any idea's why the code wouldn't give consistent results to begin with but then work as expected after the fith tab.

Many thanks...