Search through files in folder and return rows containing specific text and also workbook name

[Martynrbell]

Hi All,

I have a folder containing .xlsx files.
"C:\Masterfolder"

I would like to build a macro that opens each file and searches column B the cells for text containing "SA2" and return the row back to one workbook

I also need the work book name.

For example

Workbook1name
SA2 Row data
SA2 Row data
SA2 Row data
SA2 Row data
Workbook2name
SA2 Row data
SA2 Row data
SA2 Row data
SA2 Row data
Workbook2name
SA2 Row data
SA2 Row data
SA2 Row data
SA2 Row data

Is this possible?

Thanks in advance.

Martyn

 

[JLGWhiz]

assuming you have created a macro enabled workbook which will host the code.

Sub consData()
Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String, c As Range
fPath = "C:\Temp\"
fName = Dir(fPath & "*.xlsx")
Set sh = ActiveSheet
    Do
        If fName <> ThisWorkbook.Name Then
        Set wb = Workbooks.Open(fPath & fName)
            With wb.Sheets(1)
                sh.Cells(Rows.Count, 1).End(xlUp)(2) = wb.Name
                For Each c In Intersect(.UsedRange.Offset(1), .Range("B:B"))
                    If InStr(c.Value, "SA2") > 0 Then
                        c.EntireRow.Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
                    End If
                Next
            End With
        wb.Close False
        End If
        fName = Dir
    Loop While fName <> ""
End Sub

 

[Martynrbell]

Good Morning,
This code works great thank you.

In its current state it is returning the first SA2 value. some sheets may have 1-5 SA2 values, is there a way to get it to return them all.

Thanks Again

 

[JLGWhiz]

The code, as currently written, will return any row where "SA2" appears anywhere in a cell in column B of the Sheet 1 of the designated Workbook . You can test it by creating a sheet with more than one cell in column B that has SA2 in them.