Hyperlink Worksheet Names

I have several Formula and Function Category Worksheets that have specific formula names for each.  There is a seperate worksheet for each category with their specific formula/function names in one or more columns.  How can I select those Formula / Function Names and have it automatically add a hyperlink to its specific worksheet, located within the same workbook.   The Formula/Function Name, is identical on both worksheets. The target worksheet has the name on both the Worksheet Tab and cell A1 if that makes any difference.

I actually did this manually, but when copying some of those to another worksheet, some of the hyperlinks did not transfer properly, while some did, don't know why.  So, now I want to automate the process.
Cook09Asked:
Who is Participating?
 
Arno KosterConnect With a Mentor Commented:
else try this version. the macro used to generate the hyperlinks is placed in the toc worksheet.
Links-v3.xlsm
0
 
Arno KosterCommented:
Working from the assumption that the 'separate worksheet' is called 'separate', and that in it formulae are stored in column A and corresponding worksheet names in column B, you could use this macro code to generate hyperlinks to the A1 cell of the corresponding worksheets in column C:

Sub generate_hyperlinks()
Dim row As Range
    
    For Each row In UsedRange.Rows
        Hyperlinks.Add row.Cells(3), "", row.Cells(2) & "!A1"
    Next row

End Sub

Open in new window

0
 
Cook09Author Commented:
I've posted a workbook that shows what I'd like to do.  It's still a work in progress, but the name(s) blue, I'd like to be able to select, some run into multiple columns, and then generate a hyperlink from the name to the worksheet.
Links.xlsx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Arno KosterCommented:
something like this ?

hyperlinks are placed in Cells next to the blue ones
Links-v2.xlsx
0
 
Cook09Author Commented:
No, It didn't work.  I've attached two screen shots to show the issue.  It tries to open up the file within it's original location.
Hyperlink1.png
Hyperlink2.png
0
 
Arno KosterCommented:
did you rename the file ?
0
 
Cook09Author Commented:
No I didn't, should I have?
0
 
Cook09Author Commented:
Yes, that does seem to work. I need to check it out on some other pages, but what you had did link to the three pages that was supplied.  Do you know why this worked and the other's didn't.
0
 
Cook09Author Commented:
I have some names that need a '  ' around them, as they have spaces in them.  Where is the best place to put those?
0
 
Arno KosterCommented:
the difference is in the way the hyperlinks are made.
first try used hyperlinks as formulae where we need to put the worksheet name inside of the formula for the hyperlink to work. this will screw things up.

the last try used macro-code inserted hyperlinks where we can leave the worksheet name out of the hyperlink and it works independantly of the worksheet name. so even worksheets with spaces in the name will work as expected using this approach.
0
 
Cook09Author Commented:
Okay, I understand.  The key though is that the names have to match exactly.  I appreciate your help. It definitely saved me a ton of time, with over 340 worksheets.
0
 
Cook09Author Commented:
Understanding of how hyperlinks work really helped in this case.
0
 
Cook09Author Commented:
Actually, I tried the code with a space between two of the names.  It didn't work. "Invalid Reference." I had to still put a _ in between the two words.
0
All Courses

From novice to tech pro — start learning today.