[VBA] Create Hyperlink to latest worksheet in Workbook

[PingPongMan]

Hey guys,

as part of a larger macro, I want to insert a code that creates a link to the latest sheet of the workbook and name it according to the text in the selcted cell.
This was asked in a simliar way before (https://www.mrexcel.com/forum/excel...ng-hyperlink-last-worksheet-workbook-vba.html), but when I inserted the exact same code, the link saying "hello" was created but clicking on it lead to an error saying the reference is invalid, which is the same error Im getting with the code below:

Sub CreateHyperlinkToLastSheet()

Range("D43").Select
Dim x As Integer

x = ThisWorkbook.Sheets.Count

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "x!A1", TextToDisplay:=TextToDisplay:=Range("D43").Value

End Sub

If anybody could help me with that, thank you very much in advance!

Best regards!

 

[ravishankar]

Sub Macro1()
Dim a As String


     a = Sheets(Sheets.Count).Name
     Sheets(a).Range("A1") = Sheets("sheet1").Range("d43").Value
    Sheets(a).Activate
    Sheets(a).Range("A1").Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        a & "!A1", TextToDisplay:=Sheets("sheet1").Range("d43").Value
End Sub

you may modify this to suit your needs
ravi shankar