VBA: Adding hyperlink to sheet into a cell

Question

My objective is when adding a new row I want to link the cell in the lastrow + 1 in column one to a sheet that has the same name as the value. I.e. a new customer is added with customer ID 130 and a sheet is created with the same name. Now I want to add a link from the customer ID to the sheet. The data is retrieved from userform input. Perhaps I should note this sub runs after clicking a command button in my userform.

When using the code down below I get the error at the last line ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:=Sheets(cs_sht), TextToDisplay:=cs_sht Where I receive error '5 Invalid procedure or argument'. I tried fidling with the anchor using with and without selection as well as changing the sheet and anchor to activecell.

Private Sub CB_NewCS_Click()
'Copying data to table

Dim rng As Range
Dim LastRow As Long
Dim cs_sht As String
Dim ws As Worksheet
Dim Ws_K As Worksheet

NewCS.Hide

' Setting ranges and sheets
Set rng = Sheets("Kundeliste").ListObjects("Tabel_Kunde").Range
Set Ws_K = Sheets("Kundeliste")

' Searching for new input line
LastRow = rng.Find(What:=Ó * Ó, _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

' Inserting userform data
With rng.Parent
    .Cells(LastRow + 1, 1).Value = Application.WorksheetFunction.Max(rng.Columns(1)) + 1 ' Customer ID in ascending order
    .Cells(LastRow + 1, 2).Value = TB_Firstname.Value ' First name
    .Cells(LastRow + 1, 3).Value = TB_Lastname.Value ' Last name
    .Cells(LastRow + 1, 4).Value = TB_Phone.Value ' Phone number
    .Cells(LastRow + 1, 5).Value = TB_Address.Value ' Address
    .Cells(LastRow + 1, 6).Value = TB_Housenr.Value ' House / road number
    .Cells(LastRow + 1, 7).Value = TB_Floornr.Value ' Floor nr.
    .Cells(LastRow + 1, 8).Value = TB_Zipcode.Value ' Zipcode / postal code
    .Cells(LastRow + 1, 9).Value = TB_City.Value ' City / town
    .Cells(LastRow + 1, 10).Value = LB_Product.Value ' Product for the customer
    ' Checkbox values:
    .Cells(LastRow + 1, 12).Value = -Chb_Contact.Value
    .Cells(LastRow + 1, 13).Value = -Chb_Meet1.Value
    .Cells(LastRow + 1, 14).Value = -Chb_Accept.Value
    .Cells(LastRow + 1, 15).Value = -Chb_Meet2.Value
    .Cells(LastRow + 1, 16).Value = -Chb_Revision.Value
    .Cells(LastRow + 1, 17).Value = -Chb_Contact2.Value
    .Cells(LastRow + 1, 18).Value = -Chb_Followup.Value
    cs_sht = .Cells(LastRow + 1, 1).Value
End With

With ThisWorkbook
    Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    ws.Name = cs_sht
End With
Ws_K.Activate
Ws_K.Range(Ws_K.Cells(LastRow + 1, 1), Ws_K.Cells(LastRow + 1, 1)).Select
' OBS OBS OBS ERROR OCCURS HERE vvvvvvvv
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:=Sheets(cs_sht), TextToDisplay:=cs_sht


End Sub

Answer 1

Subaddress must be a string referring to a cell so you would need something like this

SubAddress:=cs_sht & "!A1"

The macro recorder is useful for working out this sort of syntax.

Answer 2

You need something of the form:

SubAddress: = somestring

like:

SubAddress:= "Sheet2!B9"

The string represents a sheet-cell combination.