Search tables on multiple sheets for a string

[thedoccontroller]

I'm hoping to search a WB(multiple sheets) for a thread entered in a message box and either return the name of any table the text is found in, or activate the sheet if only found in one table. I know this is complex, but any advice would be appreciated. Screen shot of one sheet with the column of one table I want to search:

 

[Puertorekinsam]

I'm not sure where your tables are or how many tables could appear on a sheet. or where you want the data to go.... but maybe this will be a good start for you:

I assume there is one table on each sheet. I go through all the sheets look for the code phrase. if the code phrase is found once it opens that sheet. If the code is found more than once it list it in column A of the first sheet in the workbook.

Sub FindMatchingTables()
Dim ws As Worksheet
Dim Matches As Integer
Matches = 0
Dim Matched
Dim FindMe As String
Dim MatchedGroup As String
MatchedGroup = ""
FindMe = InputBox("Search for this:")


For Each ws In ThisWorkbook.Worksheets

    Set Matched = ws.Cells.Find(FindMe)
    
    If Not Matched Is Nothing Then
        Matches = Matches + 1
        If MatchedGroup = "" Then MatchedGroup = ws.Name Else MatchedGroup = MatchedGroup & ";" & ws.Name
        
        x = x
    End If

Next
If Matches = 1 Then
ThisWorkbook.Worksheets(MatchedGroup).Activate
Else
    Dim Individuals
    Individuals = Split(MatchedGroup, ";")
    
    Dim i As Integer
    For i = 1 To Matches
    Sheet1.Cells(i, 1) = Individuals(i - 1)
    Next
    
End If
End Sub

 

[thedoccontroller]

Short answer is that there will be 2 tables on most sheets, none on some. As the other info on the sheet is variable, the location of the table is as well, but if the code is not searching tables, specifically, we should be okay. The code looks great, and I can prolly build on it if necessary. I'm gonna plug it in in just a bit, but will get back to you next week. Thank you so much!

 

[Puertorekinsam]

Cool..

also you can remove that x=x line... I used that to mark a break to make sure it was doing what I expected and forgot to take it out. Ha ha.

 

[thedoccontroller]

So... It worked beautifully until I attached it to a button. Now if I enter nothing in the box, it returns all the pages, as one would expect. However, if I enter a string I know exists, I get nothing. Before adding it to a button, it would return the 2 sheets that it was on(strings should appear in a minimum of 2 sheets)

 

[thedoccontroller]

Okay... so I changed to report to column to 15(O) and that works fine. Changed the row to 4, and that worked as I said before. The first time I ran it, it worked fine, but now will not work. Works if I leave the row at 1, or if I make it row 4, it only returns results for " ". No strings. Really weird.

 

[thedoccontroller]

Just adjusted the sheet so the results can report to row 1 and all is well. Thank you so much