Hyperlinks in Workbook not playing!!!

[Taff]

I create an index page, of a large work book, with all the relevant information I need and create hyperlinks for each individual sheet. However when trying to activte the link I get the following message - Reference is not Valid.

The code I am using to create the links is below, this is part of a much larger code.

When I correct the link manually it appears that all are missing ' ' at either end of the title.

Can anybody point me in the correct direction.

Many Thanks

Taff

SheetCount = ActiveWorkbook.Sheets.Count
Sheets("sheet index").Activate
Range("A1").Select
For i = 1 To SheetCount
If Sheets(i).Name <> "Index Sheet" Then
ActiveCell.Offset(i, 0).FormulaR1C1 = Sheets(i).Name
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(i, 0), Address:="", SubAddress:= _
Sheets(i).Name & "!A1"
End If
Next i

 

[Ekim]

You will get that error message if you have spaces in your sheet names. Try this revision:

Sub myLinks()
SheetCount = ActiveWorkbook.Sheets.Count
Sheets("Index Sheet").Activate
Range("A1").Select
For i = 1 To SheetCount
If Sheets(i).Name <> "Index Sheet" Then
ActiveCell.Offset(i, 0).FormulaR1C1 = Sheets(i).Name
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(i, 0), _
Address:="", SubAddress:= _
"'" & Sheets(i).Name & "'!A1"
End If
Next i
End Sub

Note that you have an inconsistency in your original macro:

Sheets("sheet index").Activate

If Sheets(i).Name <> "Index Sheet" Then

I assume that it should be “Index Sheet” in both spots.

HTH

Mike

 

[Taff]

Mike,

Thanks, had to tweak slightly for it to work in my complete code, but it done the job!!

Thanks again

Taff