Forum Discussion
filipiovitu
May 01, 2024Copper Contributor
Search for a value in entire workbook, return the entire row where value was found
Hello All, Example: I have 10 Invoice numbers (C093705926 C093705927 C093725527 C093750757 C093750759 C093769955 C093769956 C093789382 C093808525 C093808526) and need to search these in a workboo...
- May 01, 2024
Try this macro:
Sub ListEm() Dim wbkSource As Workbook Dim wshSource As Worksheet Dim rngSource As Range Dim strAddress As String Dim wbkTarget As Workbook Dim wshTarget As Worksheet Dim varSearch As Variant Dim lngRow As Long Application.ScreenUpdating = False Set wbkSource = ActiveWorkbook Set wbkTarget = Workbooks.Add(xlWBATWorksheet) Set wshTarget = wbkTarget.Worksheets(1) For Each wshSource In wbkSource.Worksheets For Each varSearch In Array("C093705926", "C093705927", "C093725527", _ "C093750757", "C093750759", "C093769955", "C093769956", _ "C093789382", "C093808525", "C093808526") Set rngSource = wshSource.Range("C:C").Find(What:=varSearch, LookAt:=xlWhole) If Not rngSource Is Nothing Then strAddress = rngSource.Address Do lngRow = lngRow + 1 rngSource.EntireRow.Copy Destination:=wshTarget.Range("A" & lngRow) Set rngSource = wshSource.Range("C:C").Find(What:=varSearch, _ After:=rngSource, LookAt:=xlWhole) If rngSource Is Nothing Then Exit Do Loop Until rngSource.Address = strAddress End If Next varSearch Next wshSource Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
filipiovitu
May 02, 2024Copper Contributor
Thank you, where should I insert the names of the workbooks? Also, can I use this to search up more than just 10 invoice numbers?
HansVogelaar
May 02, 2024MVP
The code assumes that the workbook with the sheets to be searched is the active workbook.
You can add more invoice numbers to the array in the code.
- HansVogelaarMay 02, 2024MVP
Yes - if you want to keep the macro for later use, save the workbook as a macro-enabled workbook (*.xlsm)
- filipiovituMay 02, 2024Copper ContributorThank you, it works great. Do I need to save my workbook as Macro Enabled in order to store the macro?