Hyperlink to cell of same text value in different sheet

kiwichick

New Member
Joined
Feb 16, 2007
Messages
9
I hope this makes sense. I have a workbook (Excel 2007) where one of the worksheets is named TV and another one is named Other. Column A in both worksheets contain titles of TV Shows. Some of the titles are the same in both worksheets but they won't necessarily share the same cell reference. (For example: Brokenwood Mysteries may be cell A15 in TV but cell A7 in Other.) The titles are listed in alphabetical order so as new titles are added to both worksheets the cell references of the titles change. Is it possible to create a hyperlink where I click a title in TV and it takes me to the same title in Other? Thanks in advance for any help 🙂
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,325
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I supposed your second sheet name is Sheet2 then Try this:

VBA Code:
Sub AddHyperlinks()
Dim i As Long, Lr1 As Long, Lr2 As Long, Cr As Long, CrA As String
Lr1 = Sheets("TV").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
On Error GoTo NextC
 For i = 2 To Lr1
On Error GoTo NextC
    Cr = Application.WorksheetFunction.Match(Range("A" & i).Value, Sheets("Sheet2").Range("A1:A" & Lr2), 0)
    CrA = Range("A" & Cr).Address
    Sheets("TV").Hyperlinks.Add Anchor:=Sheets("TV").Range("A" & i), Address:="", SubAddress:= _
    "'" & Sheets("Sheet2").Name & "'!" & CrA, TextToDisplay:=Sheets("TV").Range("A" & i).Value
NextC:
Resume NextD
NextD:
Next i
End Sub
 
Solution

kiwichick

New Member
Joined
Feb 16, 2007
Messages
9
I supposed your second sheet name is Sheet2 then Try this:

VBA Code:
Sub AddHyperlinks()
Dim i As Long, Lr1 As Long, Lr2 As Long, Cr As Long, CrA As String
Lr1 = Sheets("TV").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
On Error GoTo NextC
For i = 2 To Lr1
On Error GoTo NextC
    Cr = Application.WorksheetFunction.Match(Range("A" & i).Value, Sheets("Sheet2").Range("A1:A" & Lr2), 0)
    CrA = Range("A" & Cr).Address
    Sheets("TV").Hyperlinks.Add Anchor:=Sheets("TV").Range("A" & i), Address:="", SubAddress:= _
    "'" & Sheets("Sheet2").Name & "'!" & CrA, TextToDisplay:=Sheets("TV").Range("A" & i).Value
NextC:
Resume NextD
NextD:
Next i
End Sub
Thanks for that. But can you please also tell me where to apply the code? 🙂
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,325
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
If you don't familiar with VBA, DO this work step to Step:
1. Press ALT+F11
2. At the windows that open ,Press ALT+I then M (or Go to Insert , then Module)
3. at the small window open now , Paste my code
4. Close VBA window and Back to Excel window ( at sheet that you want add hyperlinks I think TV sheet)
5. Press ALT + W then M then V ( Or Go to View tab, Macros, View Macros)
6. Select AddHyperlinks and then Press Run.
AND
7. if you want maintain code for next uses, you should save as excel file as macro-enabled workbook (.xlm or .xlsm)
 

kiwichick

New Member
Joined
Feb 16, 2007
Messages
9

ADVERTISEMENT

If you don't familiar with VBA, DO this work step to Step:
1. Press ALT+F11
2. At the windows that open ,Press ALT+I then M (or Go to Insert , then Module)
3. at the small window open now , Paste my code
4. Close VBA window and Back to Excel window ( at sheet that you want add hyperlinks I think TV sheet)
5. Press ALT + W then M then V ( Or Go to View tab, Macros, View Macros)
6. Select AddHyperlinks and then Press Run.
AND
7. if you want maintain code for next uses, you should save as excel file as macro-enabled workbook (.xlm or .xlsm)
Thank you again, I am a little bit familiar with VBA but I haven't worked with modules/macros before 🙂 I tried your code and it throws an error: Run-time error '9' Subscript out of range. Debug points to this line:
VBA Code:
Lr2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,325
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I supposed your second sheet name is Sheet2 then Try this:
I think your target sheet name isnot Sheet2 then change Sheet2 at all of above code (full code) to your second sheet name.
 

kiwichick

New Member
Joined
Feb 16, 2007
Messages
9
I think your target sheet name isnot Sheet2 then change Sheet2 at all of above code (full code) to your second sheet name.
Oh of course! Yes Sheet2 is actually named Other. I've changed your code accordingly and it seems to be working just fine. Thank you so much!
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,325
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're welcome & Thanks for Feedback.
 

Forum statistics

Threads
1,141,070
Messages
5,704,112
Members
421,327
Latest member
Msh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top