Loop through files in directory, open, filter, copy and paste to Book 1, then close and repeat

[bran8989]

Any idea how to put that in code? Any form of example would be helpful. The files are located on my C (local) drive and all have the same name with the exception of a month/year at the end of the file name.

The filtering will be done solely for Column A for the keyword "Actual". I want to copy the filtered rows into a new workbook (call it workbook Book1).

Then I want to close the existing file and repeat the same process. There are about 10 files in total.

Please let me know if you have any suggestions.

 

[ravishankar]

Hi, try the following macro codes

Sub Pull_actual()
Dim a As Long
Dim f As String, b As String, x As Integer


Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"


f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Workbooks.Open Filename:=Cells(1, 2) & f
x = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & x).AutoFilter Field:=1, Criteria1:="=actual", _
Operator:=xlAnd
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
ActiveWorkbook.Close
x = Cells(Rows.Count, 2).End(xlUp).Row
Range("C" & x + 1).PasteSpecial
Loop


MsgBox "Listing is complete"
End Sub

If it works as expected, it lists file names in your folder, opens each of them, filters column A for "Actual" and copy them and paste it to the main book. Try it out
Ravi shankar