Macro to create Hyperlinks of a new page

[digixu]

Hi i am pretty new to excel and i am propably way in over my head any help would be appriacted, ok i've made a payroll system our staff the front page lists all the staff down with the first name at A8 and the last employee at A18 the worksheet is called home


ive created a macro to create a new employee it copies the master sheet, goes to home inserts a line under the last employee types in rename employee here copies some of the forumlas and what i am trying to get it to do is to create a hyperlink that links to that new page i created problem is it works first time but the second time the macro is pressed it links the new new employee to the old emp

Sub newcleaner()
'
' newcleaner Macro
'

'
    Sheets("1").Select
    Sheets("1").copy After:=Sheets(12)
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "rename"
    Sheets("Home").Select
    Rows("19:19").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A19").Select
    ActiveCell.FormulaR1C1 = "new cleaner"
    Range("B19:H19").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("I18").Select
    Selection.AutoFill Destination:=Range("I18:I19"), Type:=xlFillDefault
    Range("I18:I19").Select
    Range("A19").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'1 (2)'!A1", TextToDisplay:="new cleaner"
    Range("I22").Select
End Sub

[JBeaucaire]

The goal is to NAME each sheet uniquely to match the name of the cleaner being added, that makes it easy to match:

Option Explicit

Sub newcleaner()
Dim NewName As String

NewName = Application.InputBox("Enter the name of the employee being added:", "Name", "John Doe", Type:=2)
If NewName = "False" Then Exit Sub
If Evaluate("ISREF('" & Left(NewName, 29) & "'!A1)") Then
    MsgBox "This sheet exists already"
    Exit Sub
End If

    Sheets("1").Copy After:=Sheets(Sheets.Count)
    Range("A2").Value = NewName
    ActiveSheet.Name = Left(NewName, 29)
    
    With Sheets("Home")
        .Activate
        .Rows("19:19").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("B19:H19").Borders.Weight = xlThin
        .Range("I18").Copy .Range("I19")
        .Hyperlinks.Add Anchor:=.Range("A19"), Address:="", _
            SubAddress:="'" & Left(NewName, 29) & "'!A1", TextToDisplay:=NewName
    End With
    
End Sub

[digixu]

Thank you so much it works perfectly.