Looking for an Excel VBA Macro that can review all the worksheet tabs against a single tab and find matching text values

[kdjonesmtb]

Looking for an Excel VBA Macro that can review all the worksheet tabs against a single source text column

For example -- Field Name -- compared to any occurrence of the same name in any worksheet and provide a summary sheet that indicates the match and the source worksheet name and location




Thanks

 

[kdjonesmtb]

Or is there VLookup or Index Matching solution that work on the entire workbook -- I have 132 worksheets in the workbook

 

[kdjonesmtb]

I found this code that works within the same workbook --- is it possible to change this code to have it find matches from one workbook and see if they exist in other workbooks



'Uses Range.Find to get a range of all find results within a worksheet

' Same as Find All from search dialog box

'

Function FindAll(rng As Range, What As Variant, Optional LookIn As XlFindLookIn = xlValues, Optional LookAt As XlLookAt = xlWhole, Optional SearchOrder As XlSearchOrder = xlByColumns, Optional SearchDirection As XlSearchDirection = xlNext, Optional MatchCase As Boolean = False, Optional MatchByte As Boolean = False, Optional SearchFormat As Boolean = False) As Range

Dim SearchResult As Range

Dim firstMatch As String

With rng

Set SearchResult = .Find(What, , LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

If Not SearchResult Is Nothing Then

firstMatch = SearchResult.Address

Do

If FindAll Is Nothing Then

Set FindAll = SearchResult

Else

Set FindAll = Union(FindAll, SearchResult)

End If

Set SearchResult = .FindNext(SearchResult)

Loop While Not SearchResult Is Nothing And SearchResult.Address <> firstMatch

End If

End With

End Function