VBA - extracting multiple worksheets from multiple workbooks to another workbook based on condition

[Momo3005]

Hi,

I'm trying to extract data from multiple worksheets from over 30 workbooks from a same path to a another master workbook. For instance, if cell D9 in the different worksheets is equal to "X", I want to extract data from specific cells for all those worksheets.

I am very new to VBA and I've tried this code and it doesnt work. Please help! Thanks a lot!

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim ws As Worksheet
Dim Filepath As String
Filepath = "D:\Users\xxx"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = "Zmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
For Each ws In Worksheet
If ws.Cells(9, 4) = "X" Then
ws.Range("I30:I32").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4))
End If
Next ws
MyFile = Dir
Loop
End Sub