try changing

WhereToGo = ActiveCell.Value & "!A1"


to

WhereToGo = "'" & ActiveCell.Value & "'!A1"
Lightly tested, but this worked for me:

Sub AddHyperlinks()
Dim ScreenTipMsg As String
Dim WhereToGo As String

Range("A7").Activate
KeepGoing = "Y"

Do While KeepGoing = "Y"

iRow = Selection.Row
iStartCol = "A"
iEndCol = "B"

With Worksheets(1)

ScreenTipMsg = "Go To " & ActiveCell.Value & " Sheet"
If ActiveCell.Value Like "*[!A-Za-z0-9]*" Then
WhereToGo = "'" & ActiveCell.Value & "'!A1"
Else
WhereToGo = ActiveCell.Value & "!A1"
End If
Range(Cells(iRow, iStartCol), Cells(iRow, iEndCol)).Select

.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=WhereToGo, ScreenTip:=ScreenTipMsg
End With

ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < " " Then
KeepGoing = "N"
End If

Loop

End Sub

--
Regards,
Tom Ogilvy



"Notawahoo" wrote in message
oups.com...
Thanks to this group (Brian Wilson) many years ago, I have some VBA
code which goes through a multi-tab workbook and creates a list of
hyperlinks on the first sheet which go directly to any of the tabs.
There are about 20 tabs, so you can't see them all at once, and the
hyperlinking is really useful.

My problem is that if a tab name has any special characters or spaces
in it, then the vba-generated hyperlink doesn't work. If I manually
go into "edit hyperlink" and click on the tab name, then it works
fine.

The hyperlink to tab DOCS works fine, and the one to tab ADM-SRC does
not work -- it says "reference is not valid".

I'm using Excel 2003.

Here's the vba code:

Sub AddHyperlinks()
Dim ScreenTipMsg As String
Dim WhereToGo As String

Range("A7").Activate
KeepGoing = "Y"

Do While KeepGoing = "Y"

iRow = Selection.Row
iStartCol = "A"
iEndCol = "B"

With Worksheets(1)
ScreenTipMsg = "Go To " & ActiveCell.Value & " Sheet"
WhereToGo = ActiveCell.Value & "!A1"
Range(Cells(iRow, iStartCol), Cells(iRow, iEndCol)).Select

.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=WhereToGo, ScreenTip:=ScreenTipMsg
End With

ActiveCell.Offset(1, 0).Select
If ActiveCell.Value < " " Then
KeepGoing = "N"
End If

Loop

End Sub


Thanks in advance for any help you can offer!

Nancy