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

Martynrbell

New Member
Joined
Apr 11, 2016
Messages
36
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
assuming you have created a macro enabled workbook which will host the code.
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

New Member
Joined
Apr 11, 2016
Messages
36
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,528
Messages
5,636,852
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top