Need Help with Hyperlink

[gruhnelliot]

I want to hyperlink two numerical lists on different worksheets. Is this possible?

For example, each person in this test worksheet is assigned a number (18-01, 18-02, etc.)

WORKSHEET # 1
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Customer #
[/TD]
[TD="align: center"]First[/TD]
[TD="align: center"]Last[/TD]
[TD="align: center"]EMAIL[/TD]
[/TR]
[TR]
[TD="align: center"]18-01
[/TD]
[TD="align: center"]Ted
[/TD]
[TD="align: center"]Mosby[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18-02[/TD]
[TD="align: center"]Chris[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18-03[/TD]
[TD="align: center"]Tony[/TD]
[TD="align: center"]Adams[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18-04[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is there a way to link the customer numbers in worksheet# 1 to the corresponding numbers in worksheet # 2?

WORKSHEET # 2
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Customer #[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]April[/TD]
[/TR]
[TR]
[TD="align: center"]18-01[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]18-02[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]18-03[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]18-04[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

​

 

[My Aswer Is This]

Assuming you want each cell in sheet1 column A linked to the same cell in Sheet2 if there is some value in sheet1 column A

And by link you mean when you click on Sheet1 Range("A3") you will be taken to Sheet2 Range("A3")

Try this:

If this is not what you want please explain again.

Sub AddHyperLinks()
'Modified  3/16/2019  8:13:34 PM  EDT
Dim c As Range
With Sheets("Sheet1")
    For Each c In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=c, Address:="", SubAddress:="'" & "Sheet2" & "'!" & c.Address
    Next c
End With
End Sub

Run this script to add the links. Then just click on the cell in Sheet1 column A

 

[DanteAmor]

If the customer numbers on sheet2 are in a different row than the customer numbers on sheet1, then try the following code.
Change the data in red for your information.

Sub New_Hyperlink()
    Dim c As Range, r As Range
    Dim sh1 As Worksheet, sh2 As Worksheet
    
    Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
    For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
        Set r = sh2.Range("A:A").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not r Is Nothing Then
            sh1.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:="'" & sh2.Name & "'!" & r.Address
        End If
    Next
End Sub

 

[gruhnelliot]

My Answer Is This, your script did not work. The following error message appeared:

Run-time error '9':
Subscript out of range

I will try to re word my question.

To preface my question, the first worksheet is named CI Master List. The second worksheet is named 2019 CI Pay Summary.

In "CI Master List", row 1 are title holders. Column A is labeled "CI #". (as pictured below)

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]CI #
[/TD]
[TD="align: center"]First[/TD]
[TD="align: center"]Last[/TD]
[TD="align: center"]EMAIL[/TD]
[/TR]
[TR]
[TD="align: center"]18-01[/TD]
[TD="align: center"]Ted[/TD]
[TD="align: center"]Mosby[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18-02[/TD]
[TD="align: center"]Chris[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18-03[/TD]
[TD="align: center"]Tony[/TD]
[TD="align: center"]Adams[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18-04[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



In "2019 Pay Summary", row 1 are title holders. Column A is labeled CI #. (as pictured below).

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]CI #[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]April[/TD]
[/TR]
[TR]
[TD="align: center"]18-01[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]18-02[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]18-03[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]18-04[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0

[/TD]
[/TR]
</tbody>[/TABLE]


My goal is to click on cell A2 (18-01) in "CI Master List" and be taken to cell A2 (18-01) in "2019 Pay Summary".

I know how to hyperlink each cell individually, but Column A has hundreds of rows so I was looking for a shortcut.

 

[My Aswer Is This]

My script used the sheet names "Sheet1" and "Sheet2"

Did you try modifying these sheet named to see if things worked?

And in Sheet named "Sheet1" you need to have values in column A

See in your original post you posted this:
WORKSHEET # 1

You did not give exact sheet name. So I assumed the sheet name would be Sheet1

 

[gruhnelliot]

It worked, thank you!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

It worked, thank you!