Hello again GingerT,
Apologies. I completely missed the last part of your last post.
I would say that the easiest way to create new hyperlinks for the new sheet names would be via a separate button with the following codes assigned to it:-
Dim i As Long
For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
.Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", SubAddress:= _
"'" & .Range("A" & i).Value & "'!A1", TextToDisplay:=.Range("A" & i).Value
For i = 2 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
The ChangeNames macro will list the new sheet names in Column A of the Summary sheet with the CreateHlink macro creating the new hyperlinks to each newly named sheet. The CreateHLink macro calls the ChangeNames macro.
Following is the link to my test work book for you to peruse. Click on the "Create HLink" button to see it work:-
(You could, of course, combine all three codes to simultaneously do the required task with just one click of a button).
FYI. Excel has an inbuilt function which takes you directly to any sheet. Simply right click on the Command Bars arrows at the bottom left of any sheet then select the required sheet from the list that appears.
I hope that this helps.