Hyperlink to cell of same text value in different sheet

[kiwichick]

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 ?

 

[maabadi]

I supposed your second sheet name is Sheet2 then Try this:

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

 

[kiwichick]

I supposed your second sheet name is Sheet2 then Try this:

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]

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]

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:

Lr2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

 

[maabadi]

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]

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]

You're welcome & Thanks for Feedback.

 

[gazmoz17]

Hi, thanks for this code and is basically an orig question I was about to ask.

But in my column A which are product codes, its only creating hyperlinks for my product codes which look like text/alphanumeric but not those which prod codes which are only numeric. Ive tried changing the number formatting but makes no difference.

Additionally is there a way to have corresponding hyperlinks on my sheet 2 back to sheet 1?

Many Thanks