Link tab name to cell

October 28, 2009 at 08:02:43
Specs: Windows XP
II'm quite new on here, actually completely fresh so please excuse me if this has been covered already.

I'm trying to get 58 tabs, all of which contain the same report, linked to a list on a contents sheet at the front.

There has already been a posting before but i cant reply to it as its too old.

I followed these instrustions:

Sub Hyperlinker()
For Each cName In Sheets(1).Range("A1:A4")
ActiveSheet.Hyperlinks.Add Anchor:=cName, Address:="", SubAddress:= _
cName & "!A1", TextToDisplay:=cName.Value
End Sub

But what i've found is that it doesn't hyperlink to the sheet because it is shown as 'The Sheet' instead of The Sheet.

I'm hoping someone can help because ive gone through the 58 sheets twice now, once to link the sheet to the title and another to get the same name on the tab.

Its too fustrating!

See More: Link tab name to cell

Report •

October 28, 2009 at 08:25:35
I'm not sure what you are doing wrong, but I ...

1- Copied the code from your post into a new workbook
2 - Added a Sheet4
3 - Entered Sheet1, Sheet2, Sheet3 & Sheet4 into Sheet1!A1:A4

When I ran the code it created links to all 4 sheets.

Is that the process you are following?

(I posted that code a while back, so I was pretty sure it was going to work.)

Report •

October 28, 2009 at 09:10:33

Try this.

It gets all the worksheet names and puts the names in the Active worksheet starting
at Cell A1 and adds the hyperlinks.

No need to enter the 58 names.

Sub SheetsHyper()
Dim objSht As Worksheet
Dim strSht As String
Dim n As Integer
n = 0

For Each objSht In ActiveWorkbook.Worksheets
    strSht = objSht.Name
    ActiveSheet.Range("A1").Offset(n, 0).Value = strSht
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Range("A1").Offset(n, 0), Address:="", SubAddress:= _
    strSht & "!A1", TextToDisplay:=strSht
    n = n + 1
End Sub


Report •

October 29, 2009 at 07:43:40
Hi guys,

Thanks for the quick response.
I tried tre above code and it brought up the list but provided the same hyperlinking error.
I have subsequently editted all 58 links so that they go to the right place.

When using your code Humar, as with DerbyDad03's previously posted code, it linked the cells but when you hover over the cell the tab name does not start with and end with '.
When I edit hyperlink all the tab names are i.e. 'The Queenswood'. This may have been because I copied 1 tab to create the other 57 as the first on was without the '.

Either way I added a ' to the start and end of every name in the list and it worked

The code above was very helpful, thank you!

Report •

Related Solutions

October 29, 2009 at 08:13:50

Glad we were able to help.

Still puzzled by the ' issue.

I thought that Tab names could not start with a '
If your tab names start with a ' then that may explain the error you have been seeing.

Anyway ... as long as it works ...


Report •

Ask Question