Dynamic Hyperlinks when creating new Sheets

Question

I have some code that has the user input a name in an input box which then creates a sheet with that name. This happens when the user presses a button on a sheet called 'Home.' Please see code below, What I need is for a hyperlink to be created at the same time on the 'home' sheet in the A column utilzing the lastRow function. I have most of this working, only the dynamic hyperlink keeps being created in cell A8 on the 'home' sheet, I want the new link to be created 2 cells below the prior link created.

Sub add_new_sheet()

'''Input Box for Unit Name

Dim i As Variant
Dim LastRow As Long
Dim LastRow2 As Long
Dim shtA As Worksheet
Dim shtB As Worksheet

Set shtA = Worksheets("home")
Set shtB = Worksheets("Base Data")
LastRow = shtB.Cells(shtB.Rows.Count, "A").End(xlUp).Row + 1
LastRow2 = shtA.Cells(shtA.Rows.Count, "A").End(xlUp).Row + 2

i = InputBox("Enter Name of Unit")
'shtA.Cells(LastRow, 1).Value = i
shtB.Cells(LastRow, 1).Value = i

Dim sht_N As Worksheet

Set sht_N = ActiveWorkbook.Sheets("CoTemplate1")


'''End Unit Name

Dim Link As String
Dim oRng As Range

Link = i
Set oRng = shtA.Cells.Range("A8:A" & LastRow2 + 2)
'Set oRng = shtB.Cells(LastRow, 1)

For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(Link) Then

MsgBox "this sheet already exists"
Exit Sub
End If

Next

Sheets("coTemplate1").Visible = True
Sheets("coTemplate1").Copy after:=Sheets(Sheets.Count)


ActiveWindow.ActiveSheet.Name = Link
'Sheets("Test").Visible = True

shtA.Activate
shtA.Hyperlinks.Add oRng, "", "'" & Link & "'!A1", _
"Go to " & Link, Link

'Set oRng = Nothing

End Sub

Answer 1

Solved.

Sub add_new_sheet()

'''Input Box for Unit Name

Dim i As Variant
Dim LastRow As Long
Dim LastRow2 As Long
Dim shtA As Worksheet
Dim shtB As Worksheet

Set shtA = Worksheets("home")
Set shtB = Worksheets("Base Data")
LastRow = shtB.Cells(shtB.Rows.Count, "A").End(xlUp).Row + 1
LastRow2 = shtA.Cells(shtA.Rows.Count, "A").End(xlUp).Row

i = InputBox("Enter Name of Unit")
shtB.Cells(LastRow, 1).Value = i

Dim sht_N As Worksheet

Set sht_N = ActiveWorkbook.Sheets("CoTemplate1")

'''End Unit Name

Dim Link As String
Dim oRng As Range

Link = i
Set oRng = shtA.Cells(LastRow2 + 2, 1)

For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(Link) Then

MsgBox "this sheet already exists"
Exit Sub
End If

Next

Sheets("coTemplate1").Visible = True
Sheets("coTemplate1").Copy after:=Sheets(Sheets.Count)

ActiveWindow.ActiveSheet.Name = Link

shtA.Activate
shtA.Hyperlinks.Add oRng, "", "'" & Link & "'!A1", _
"Go to " & Link, Link

End Sub