|First, a posting tip:|
Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. Thanks!
As for your question, the code works just fine for me.
I'm not sure what you mean by this:
"...and that tab is named and not called Sheet1"
I know what you are saying but the code doesn't refer to a sheet named Sheet1, it refers to Sheet(1) which is the first sheet in the workbook. It's name doesn't matter, so I'm not sure why you added that information.
Are you really getting an "Invalid Reference" message or are you getting a "Reference is not valid" message?
The only way I can cause a "Reference is not valid" message is if there isn't a sheet that is named exactly the same as the text in one of the hyperlink cells.
You could try some code like this to see if the Sheet names match the values in the cells:
'Loop through Column C and compare text to Sheet names
For sht = 2 To 13
If Sheets(sht).Name <> Sheets(1).Range("C" & sht + 2) Then _
MsgBox "There is no sheet named " & Sheets(1).Range("C" & sht + 2)
You could also let VBA put the text in Column C and create the links directly from the Sheet names so that they would always be an exact match.
'Loop through Sheet 2 through last Sheet
For sht = 2 To Sheets.Count
'Put sheet name in Column C
Sheets(1).Range("C" & sht + 2).Value = Sheets(sht).Name
'Create hyperlink to sheet
Sheets(1).Range("C" & sht + 2).Hyperlinks.Add _
Anchor:=Sheets(1).Range("C" & sht + 2), Address:="", SubAddress:= _
Sheets(sht).Name & "!A1", TextToDisplay:=Sheets(sht).Name
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.