Thanks [USER="263823"]Luke M[/USER]. That was it! Works like a champ.
Posts by Benjie McCall
-
-
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*" _
ThenS.Select
' 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]
[INDENT=2]With Sheets("Index")
.Select
.Hyperlinks.Add Anchor:=ActiveSheet.Cells(TOCRow, 1), Address:="", _
SubAddress:=LinkName, TextToDisplay:=ThisName
End With[/INDENT]
[INDENT]
TOCRow = TOCRow + 1 'Bump row nbr
End If[/INDENT]
Next S