Excel Macro that Searches Entire Workbook and Returns All Matches
This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the matching results to a single worksheet. You do not have to specify a specific lookup_table and the data can be located anywhere on the worksheets and it will still be found and returned with this macro.
Sub Return_Results_Entire_Workbook() 'This does not search the worksheet that will contain the results of the search ' |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| ' |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| ' ||||||||||||||||||| ------------ TeachExcel.com -------------- ||||||||||||||||||||||||| ' |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| ' |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 'Number for the worksheet that contains the value for which to search searchValueSheet = "Sheet1" 'Get the value for which we need to search into the macro searchValue = Sheets(searchValueSheet).Range("A2").Value 'how many columns to the right of any "found" value that you want to use to return the data returnValueOffset = 1 'The sheet where the results should be placed outputValueSheet = "Sheet1" 'The column in the sheet where the results should be placed outputValueCol = 2 'The row in the sheet where the results should be placed 'everything from this row down must be empty! outputValueRow = 2 'clear the results display area Sheets(outputValueSheet).Range(Cells(outputValueRow, outputValueCol), Cells(Rows.Count, outputValueCol)).Clear 'count the worksheets in the workbook wsCount = ActiveWorkbook.Worksheets.Count 'loop through the worksheets in the workbook For i = 1 To wsCount 'Don't search the sheet with the lookup value or returned values - assumes source data will be on other tabs. If i <> Sheets(searchValueSheet).Index And i <> Sheets(outputValueSheet).Index Then 'Perform the search, which is a two-step process below Set Rng = Worksheets(i).Cells.Find(What:=searchValue, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then rangeLoopAddress = Rng.Address Do Set Rng = Sheets(i).Cells.FindNext(Rng) Sheets(outputValueSheet).Cells(Cells(Rows.Count, outputValueCol).End(xlUp).Row + 1, outputValueCol).Value = Sheets(i).Range(Rng.Address).Offset(0, returnValueOffset).Value Loop While Not Rng Is Nothing And Rng.Address <> rangeLoopAddress End If End If Next i End Sub
The code above may seem confusing but you really only have to change a few things to get it to work with your data.
You will have to tell the macro which worksheet contains the value you are searching for, where that search value is located, on which worksheet you want to return the data once it is found and where within that worksheet you want to display the data.
First, change the searchValueSheet to the name of the worksheet that contains the value for which you want to search, the searchValue.
The searchValue is the cell reference of the cell that is used to locate the data to return. Change A2 to the reference of the cell that contains the value you are searching for or the cell where you will input that value. Remember, this cell should be located on the searchValueSheet mentioned above.
The returnValueOffset is a very important value. This tells the macro how far to the right to go to find the data that you want to return once a match for the searchValue has been found. Note that the returned data must come from the same row as the data that matches the searchValue.
The outputValueSheet is the name of the worksheet where you want to return the data. Change the name from Sheet1 to whatever you need. This can be the same as the searchValueSheet or different, it doesn't matter.
The outputValueCol is the column where you want to display the results within the outputValueSheet
The outputValueRow is the first row in which the returned results should be displayed in the outputValueCol on the outputValueSheet.
Anothing important thing to note is that this macro will NOT search through the worksheets that are referenced by the searchValueSheet or the outputValueSheet. This should not usually matter but, if it does, the easiest solution is to create a specific "Search" tab and set the macro to return everything there.
It may seem like a lot to change, but at least I made it easy for you! ;) And, once you set this macro up to work the way you want, it will save you TONS of time.
I hope this helps! :)
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.