Hello-
Utilizing the below code, i am able to search for a key word, and resulting tab names with that keyword are provided in the cell directly to the right. I am looking to manipulate this so I can actually click on the resulting tab name(s), and I will be brought to that specific tab.
I have attached a picture of the cover page I am using to do this.
Code for the search function is as follows:
Sub Search()
' MKM '
Dim data, sh, r_count As Long, fsearch As Range
With Sheets(" Cover Sheet")
If .Range("B12") = "" Then Exit Sub
With .Range("B12", .Cells(Rows.Count, "b").End(xlUp).Offset(, 1))
data = .value
r_count = UBound(data)
For Each sh In ActiveWorkbook.Sheets
If sh.Name <> " Cover Sheet" Then
For i = 1 To r_count
Set fsearch = sh.UsedRange.Find(data(i, 1), , xlValues, xlPart)
If Not fsearch Is Nothing Then data(i, 2) = IIf(data(i, 2) = "", sh.Name, data(i, 2) & ", " & sh.Name)
Next
End If
Next
.value = data
End With
End With
End Sub
Thank You!
Bookmarks