Excel - Hyperlink to Tabs

Microsoft Microsoft excel 2007 upgrade
March 19, 2010 at 12:22:06
Specs: Windows 7
I'm using Windows 7... Office Pro 2007.

In Excel I have 11 tabs. Tab #1 is my title sheet, and Tab #2-#11 represent 10 different companies.

In Tab #1 I typed up my 10 company names in cells A1-A10.

I used this code:
Sub Hyperlinker()
ShtNum = 1
For Each cName In Sheets(1).Range("A1:A10")
ShtNum = ShtNum + 1
ActiveSheet.Hyperlinks.Add _
Anchor:=cName, Address:="", SubAddress:= _
Sheets(ShtNum).Name & "!A1", _
End Sub

A1,A2,A3,A6,A7 link perfectly.
A4,A5,A8,A9,A10 say "reference is invalid"

Please help!


See More: Excel - Hyperlink to Tabs

Report •

March 19, 2010 at 12:53:45

Do the names for the problem sheets have a space in the name.

If so your hyperlink will need to be in the form 'Name with Space'!A1

Hope this is the issue!


Report •

March 19, 2010 at 13:02:31
Good call!

Yes... A4,A5,A8,A9,A10 have spaces in the names.

Will A1,A2,A3,A6,A7 still work if I change the code to 'Name with Space'!A1?


Report •

March 19, 2010 at 13:21:11

Yes, you can use single quotes around names without spaces, it's just that it is required for names with spaces.

SubAddress:= "'" & Sheets(ShtNum).Name & "'!A1"
should work for all sheet names


Report •

Related Solutions

Ask Question