Search for a value in entire workbook, return the entire row where value was found

[filipiovitu]

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 workbook where they are all located.

1. Can I search for them at the same time?

2. Even if I cannot search for them at the same time, is it possible for the search to return the entire row "C093705926" is located in?

3. What happens if I have to do this for 200 invoices, will this mess up my workbook?

Thank you!

[#2]

@filipiovitu

Where are the invoice numbers located?

What should the output look like?

[#3]

The Invoice Numbers are located in Column C in sheets in the same workbook,
the output should be a literal copy of the row, which is 39 cells long

[#4]

@filipiovitu 

Thanks. How many sheets do you want to search, and is this fixed or variable?

[#5]

@Hans Vogelaar 

I'd say it would be around 4-5 sheets, but this is variable since I might need to search through more, maybe11-12.

[#6]

And thank you for taking an interest in my question, Hans!

[#7]

@filipiovitu 

Do you want to search ALL sheets except for the sheet with the output, or only specific sheets?

[#8]

I'd rather search all sheets of course, as for the sheet where the output goes, I would like that to be on a different workbook, is that possible? If not, I'll just create a new sheet each time I need to use it and copy to a different workbook. I don't need to search specific sheets, thanks

[#9 · Accepted solution]

@filipiovitu 

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

[#10]

https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-lookup-and-pull-data-from-specific/...

consolidate all sheets and query by sql

https://techcommunity.microsoft.com/t5/excel/filter-2-columns-at-the-same-time-in-a-table/m-p/412475...

multiple select checkbox

[#11]

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?

[#12]

@filipiovitu 

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.

[#13]

Thank you, it works great. Do I need to save my workbook as Macro Enabled in order to store the macro?

[#14]

@filipiovitu 

Yes - if you want to keep the macro for later use, save the workbook as a macro-enabled workbook (*.xlsm)