Solved Trying to create hyperlinks for multiple tabs in Excel

December 4, 2012 at 12:16:07
Specs: Windows 7, Intel Duo Core 2Ghz / 3 gigs
This seems to be exactly what I need. However I get Invalid Reference when I click on any of the resulting hyperlinks. All of my data is in column C and that tab is named and not called Sheet1.

Here is the code I am using. Any ideas what I screwed up?

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


See More: Trying to create hyperlinks for multiple tabs in Excel

Report •


#1
December 4, 2012 at 12:50:56
✔ Best Answer
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:

Sub CheckSheetName()
'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)
  Next
End Sub

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.

Sub LinktoSheets()
'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
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 4, 2012 at 13:54:40
Okay, I understand everything you said. But, I keep getting the Reference is not valid or the Tab names aren't found (neat macro, BTW, thanks). But when I look at the tabs the names are an exact match.

Can I send you a small sample so you can see what I'm seeing?


Report •

#3
December 4, 2012 at 13:56:28
When I use your code to pull file names in I still get the Reference is not valid error message.

Report •

Related Solutions

#4
December 4, 2012 at 15:51:37
I've sent you an email address via Private Message. The address is temporary and will be deleted once I receive your file.

Please remove any confidential information from the file before sending it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
December 6, 2012 at 04:56:18
Ffirst off, thanks a lot. I figured out my problem. I was using spaces i the tab titles so they weren't populating correctly. I added the requisite apostrophes to the code to account for that and it worked flawlessly.

Again, thanks for everything!


Report •


Ask Question