The below code performs a search in the sheets of a workbook and then lists the search results on the search page that includes the sheet name and row number where the data is located. I would like to be able to include a link to each of the search results to make it easier to go to the data if a change needed to be made. Can anyone help me with this?
Sub SearchSheets()
Const shSearch As String = "Search" '<== Your example has a space at the end of sheet name Search
Dim ws As Worksheet
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String
Dim SearchTerm As String
With Worksheets(shSearch)
.Range("f2", .Cells(Rows.Count, "h").End(xlUp).Offset(1)).ClearContents
SearchTerm = .Range("b3")
End With
For Each ws In Worksheets
With ws
If .Name <> shSearch Then
With .Range("B:B")
Set LastCell = .Cells(.Cells.Count)
End With
Set FoundCell = .Range("B:B").Find(What:=SearchTerm, After:=LastCell, LookAt:=xlPart)
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
End If
Do Until FoundCell Is Nothing
With Worksheets(shSearch).Cells(Rows.Count, "f").End(xlUp)
.Offset(1) = ws.Name
.Offset(1, 1) = FoundCell.Offset(0, 0)
.Offset(1, 2) = FoundCell.Offset(0, 1)
.Offset(1, 3) = FoundCell.Offset(0, 2)
.Offset(1, 4) = FoundCell.Offset(0, 3)
.Offset(1, 5) = FoundCell.Offset(0, 4)
.Offset(1, 6) = FoundCell.Offset(0, 5)
.Offset(1, 7) = FoundCell.Offset(0, 6)
.Offset(1, 8) = FoundCell.Offset(0, 7)
.Offset(1, 9) = FoundCell.Offset(0, 8)
.Offset(1, 10) = FoundCell.Row
End With
Set FoundCell = .Range("B:B").FindNext(After:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do
End If
Loop
End If
End With
Next
End Sub
Bookmarks