Hyperlinking cells

Please have a look at the below sub. I have created 2 such subs for different sheets. One of the 2 is working fine. But for the other I get the error message in Excel that the "Reference is not valid".

I douple checked the code and looked at the actual hyperlinks that are created (by moving the cursor over the author with the hyperlink). Everything looks OK.

Does anybody know why I get this error message in Excel?

Sub Macro1()

Dim LastRow As Long
Dim i As Long

'Make hyperlink from Authors on sheet J-Journals to sheet J-Affiliation
        
    Sheets("J-Journals").Select
    LastRow = Sheets("J-Journals").Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To LastRow
        With Worksheets("J-Journals")
            .Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", SubAddress:="J-Affiliation!A" & i
        End With
    Next i
    
End Sub

You are creating a hyerlink to a cell in a sheet named "J-Affiliation", and if you don't have that sheet in your workbook then you'd get that error message.

And you forgot to add a apostrophe ( ' ) to J-Affiliation.

        With Worksheets("J-Journals")
            .Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", SubAddress:="'J-Affiliation'!A" & i
        End With

Originally Posted by dschmitt

Strange that it worked in one but not in the other. I added the apostrophe to both subs.

The apostrophe would technically only be required as and when the sheet name warranted it - if the sheet was say "JJournals" you wouldn't need it whereas the use of hyphen in "J-Journals" necessitates it.

It's generally a good idea to always encase sheet names within apostrophes warranted or not, if not necessary XL will discard accordingly.