asked on

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.

Arno Koster

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

Cook09

ASKER

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

Arno Koster

something like this ?

hyperlinks are placed in Cells next to the blue ones
Links-v2.xlsx

Cook09

ASKER

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

Arno Koster

did you rename the file ?

Cook09

ASKER

No I didn't, should I have?

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cook09

ASKER

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.

Cook09

ASKER

I have some names that need a ' ' around them, as they have spaces in them. Where is the best place to put those?

Arno Koster

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.

Cook09

ASKER

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.

Cook09

ASKER

Understanding of how hyperlinks work really helped in this case.

Cook09

ASKER

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.