This macro will create the hyperlink in your Home Page tab after the last hyperlink in column E.
Sub NewSheetFromTemplate()
Dim sName As String
sName = Application.InputBox("Enter the new sheet name:", Title:="New Sheet Title", Type:=2)
If sName = "" Then Exit Sub
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = sName
Sheets("Home Page").Select
Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
sName & "!A1", TextToDisplay:=sName
End Sub
Bookmarks