Search multiple worksheets for matching cell value and return cell location as a hyperlink

Hello and welcome to the forum,

please check the attached example which is checking the name and week in columns 1 and 2 in all worksheets to find the address for a hyperlink: Hyperlink.xlsm
Note, I assumed that each entry in the master sheet can be found only ONCE in another sheet.

Contains:

Option Explicit

Sub GetLinks()
    Dim xlWs As Worksheet
    Dim xlRng As Range
    Dim strAddr$
    Dim i&
    
    On Error GoTo GetLinks_ErrorHandler
    Application.ScreenUpdating = False

    With ActiveSheet
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            For Each xlWs In ThisWorkbook.Worksheets
                If xlWs.Name <> .Name Then
                    Set xlRng = xlWs.Columns(1).Find(WHat:=.Cells(i, 1).Value, LookIn:=xlValues, lookat:=xlWhole)
                    If Not xlRng Is Nothing Then
                        strAddr = xlRng.Address
                        Do
                            If .Cells(i, 2).Value = xlRng.Offset(, 1).Value Then
                                .Hyperlinks.Add Anchor:=.Cells(i, 3), Address:="", _
                                    SubAddress:="'" & xlWs.Name & "'!" & xlRng.Offset(, 2).Address(False, False), _
                                    TextToDisplay:="#'" & xlWs.Name & "!" & xlRng.Offset(, 2).Address(False, False)
                                GoTo NextLine 'assuming its unique in the workbook
                            End If
                            Set xlRng = xlWs.Columns(1).FindNext(xlRng)
                            If xlRng Is Nothing Then Exit Do
                            If strAddr = xlRng.Address Then Exit Do
                        Loop
                    End If
                End If
            Next xlWs
NextLine:
        Next i
    End With

GetLinks_Proc_Exit:
    Application.ScreenUpdating = True
    Exit Sub
GetLinks_ErrorHandler:
    MsgBox "Error: " & Err.Number & " (" & Err.Description & ") in Sub 'GetLinks' of Module 'Module1'.", vbOKOnly + vbCritical, "Error"
    Resume GetLinks_Proc_Exit
End Sub

Hi,

the following is not checking the week number any more and looking for the course name in column C of the sheets:

Sub GetLinks()
    Dim xlWs As Worksheet
    Dim xlRng As Range
    Dim strAddr$
    Dim i&
    
    On Error GoTo GetLinks_ErrorHandler
    Application.ScreenUpdating = False

    With ActiveSheet
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            For Each xlWs In ThisWorkbook.Worksheets
                If xlWs.Name <> .Name Then
                    Set xlRng = xlWs.Columns(3).Find(WHat:=.Cells(i, 1).Value, LookIn:=xlValues, lookat:=xlWhole)
                    If Not xlRng Is Nothing Then
                        .Hyperlinks.Add Anchor:=.Cells(i, 3), Address:="", _
                            SubAddress:="'" & xlWs.Name & "'!" & xlRng.Address(False, False), _
                            TextToDisplay:="#'" & xlWs.Name & "!" & xlRng.Address(False, False)
                    End If
                End If
            Next xlWs
        Next i
    End With

GetLinks_Proc_Exit:
    On Error GoTo 0
    Set xlWs = Nothing
    Set xlRng = Nothing
    Application.ScreenUpdating = True
    Exit Sub
GetLinks_ErrorHandler:
    MsgBox "Error: " & Err.Number & " (" & Err.Description & ") in Sub 'GetLinks' of Module 'Module1'.", vbOKOnly + vbCritical, "Error"
    Resume GetLinks_Proc_Exit
End Sub

Hmm. Now the code isn't doing anything in your example or my workbook. It runs, but nothing populates.

Edit Disregard- it is working! I hadn't pulled over the macro into my workbook. Now I have to troubleshoot and see why it's not working for all rows, but it is for some. I'll report back.
And a +1 rep for you!!

Edit 2 And troubleshooting is finished. I think I had edited the course name on the master sheet, but not the corresponding sheet.


THANK YOU again, tehNexus!