Macro for Hyperlink list?

[Littlenia]

Hi everyone,

I have a macro that creates a new worksheet and then adds a hyperlink to a main index page, however it's adding the hyperlink to whatever cell I happen to have clicked into rather than into a list.

In the sheet "Index" I need a hyperlink for the newly created worksheet to be added under the last one already there. Is there a way to do this please?

The code I'm using at the minute looks like this:

Sub NewRecipe()

Dim strName As String

Dim strLink As String

'get the name

    'InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
    strName = InputBox("Enter Recipe Name.", "NAME COLLECTOR")
    'Exit sub if Cancel button used or no text entered
    If strName = vbNullString Then Exit Sub

    MsgBox "Creating Tab " & strName

'create new tab, rename new tab

    Sheets("Recipe Template").Copy After:=Worksheets(Worksheets.Count)
    Sheets("Recipe Template (2)").Name = strName
    Sheets("Index").Select
    Application.CutCopyMode = False

'create hyperlink to new tab

    strLink = "'" & strName & "'!A1"
    Range("A3").EntireColumn.AutoFit
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        strLink, TextToDisplay:=strName
    Range("A3").EntireColumn.AutoFit

End Sub

Thanks in advance!
Nia

 

[mumps]

This macro will add the new sheet name to the first available row in column A of Index and create the hyperlink to the new sheet.

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
    With Sheets("Index")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = strName
        strLink = "'" & strName & "'!A1"
        .Hyperlinks.Add Anchor:=.Range("A2", .Range("A" & Rows.Count).End(xlUp)), Address:="", SubAddress:=strLink, TextToDisplay:=strName
        .Columns(1).AutoFit
    End With
End Sub

 

[Littlenia]

Amazing, thank you so much!

 

[mumps]

You are very welcome.