VBA created hyperlink giving "Reference isn't valid."

  • Hoping for some help please. I have searched everywhere, including this site, but can't find an answer. And I know it's so simple but going blind trying to figure it out. I'm trying to create a simple index sheet with hyperlinks to other worksheets in the same workbook. The index sheet is updated and appears to be correct but clicking a link throws "Reference isn't valid." Stepping through debug shows LinkName with the correct worksheet name. However, viewing in Edit Hyperlink shows all links pointing to cell reference "Index" which I can't figure out how it got there.

    Any & all help very much appreciated.

    <code snippet>

    For Each S In Worksheets

    ' Excluded sheets - Not needed in index. [INDENT]If S.Visible = -1 And S.Name "Selections" And S.Name "Guide" _
    And S.Name "Cover" And S.Name "TOC" And S.Name "Index" _
    And Not S.Name Like "Sheet*" _


    ' Create visible text using footer data + cell A1 data
    ThisName = ActiveSheet.PageSetup.RightFooter + " -- " + Range("a1").Value

    ' Strip off control chars
    ThisName = Mid(ThisName, 3)

    ' Create link
    LinkName = S.Name & "!A1"
    [INDENT=2]With Sheets("Index")
    .Hyperlinks.Add Anchor:=ActiveSheet.Cells(TOCRow, 1), Address:="", _
    SubAddress:=LinkName, TextToDisplay:=ThisName
    End With[/INDENT]
    TOCRow = TOCRow + 1 'Bump row nbr
    End If[/INDENT]
    Next S

  • My guess is that you have a sheet name with a space somewhere in the name. In which case, you need to make sure you're including single quotation marks around the sheet name. Try something like this

    Best Regards,
    Luke M
    "A little knowledge is a dangerous thing."

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!