Space in Sheet Title causing Reference Not Valid for Hyperlink

[lakomitten11]

The purpose of this code is to create a hyperlink to another sheet based off of the 'HyperlinkName' variable which is the last value in the equipment list. A new sheet has already been created with the exact same name as the 'HyperlinkName' variable. The code is creating the hyperlink properly, however when there is a space in the equipment name I will receive a Reference Not Valid error, then the sheet will open.

[/COLOR]Dim R As Range
    Set R = Cells(Rows.Count, 2).End(xlUp) 'last cell in Column B with data
    If Len(R.Value) > 0 Then Set R = R.Offset(1)
    R.Value = Range("j3").Value




       HyperlinkName = Sheet4.Range("j3").Value




    Sheets("Equipment List").Hyperlinks.Add Anchor:=R, Address:="", SubAddress:= _
    HyperlinkName & "!A1", TextToDisplay:=HyperlinkName

 

[FormR]

Hi, try like this (additions in red):

    Sheets("Equipment List").Hyperlinks.Add Anchor:=R, Address:="", SubAddress:= _ 
   "'" & HyperlinkName & "'!A1", TextToDisplay:=HyperlinkName

 

[lakomitten11]

Hi, try like this (additions in red):

    Sheets("Equipment List").Hyperlinks.Add Anchor:=R, Address:="", SubAddress:= _ 
   "'" & HyperlinkName & "'!A1", TextToDisplay:=HyperlinkName

That works! Thank you!