Hyperlink Macro - alphabetical ordering?

[Littlenia]

Hi there,

Apologies if this is a stupid question, I am rather new to the world of macros and sort of winging it!

I have a macro for creating a new sheet and adding a hyperlink for that sheet on an index page which is working, but ideally I'd like my hyperlinks to always be in alphabetical order. I can of course do it manually but I'm hoping there is a more clever way to achieve this!

The code I'm currently using is below, is there any way to add something to this so that as each hyperlink is added to my "index" sheet, the list of hyperlinks on that sheet are then sorted alphabetically?

Sub NewRecipe()
    Dim strName As String, strLink As String
    strName = InputBox("Enter Recipe Name.", "NAME COLLECTOR")
    If strName = vbNullString Then Exit Sub
    MsgBox "Creating Tab " & strName
    Sheets("Recipe Template").Copy After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = strName
    Sheets("Index").Select
    With Sheets("Index")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = strName
        strLink = "'" & strName & "'!A1"
        .Hyperlinks.Add Anchor:=.Range("A" & Rows.Count).End(xlUp), Address:="", SubAddress:=strLink, TextToDisplay:=strName
        .Columns(1).AutoFit
    End With
End Sub

Thanks in advance!
Nia

 

[Peter_SSs]

Try adding this line

    With Sheets("Index")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = strName
        strLink = "'" & strName & "'!A1"
        .Hyperlinks.Add Anchor:=.Range("A" & Rows.Count).End(xlUp), Address:="", SubAddress:=strLink, TextToDisplay:=strName
        .Columns(1).AutoFit
        .Columns(1).Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes
    End With
End Sub

 

[Littlenia]

Try adding this line

    With Sheets("Index")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = strName
        strLink = "'" & strName & "'!A1"
        .Hyperlinks.Add Anchor:=.Range("A" & Rows.Count).End(xlUp), Address:="", SubAddress:=strLink, TextToDisplay:=strName
        .Columns(1).AutoFit
        .Columns(1).Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes
    End With
End Sub

Perfect! Thank you so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.