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

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