Meet criteria in one worksheet and copy row with criteria to new workbook

[aylafan]

I'm trying to figure out how to loop/search through one folder with several workbooks that contain (Sheet: Data) that meets criteria "Yes" in column "K" and then copy all those rows that meet this criteria in my Master Summary.xlsm workbook (Sheet: CapEx) starting at row 2.

I would appreciate it if anyone can help me solve this problem.

Here are several codes I used in my Excel project, but I don't know how to combine these 2 codes together to meet my above need.

Code 1: Copy rows that meets a certain criteria in one worksheet to another worksheet.

Sub CopyData()
Application.ScreenUpdating = False
Dim LR As Long, i As Long, j As Long
With Sheets("Data")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        If .Range("K" & i).Value = "Yes" Then
            j = j + 1
            .Range("A" & i & ":K" & i).Copy Destination:=Sheets("CapEx").Range("A" & j)
        End If
    Next i
End With
Application.ScreenUpdating = True
End Sub

Code 2: Loop/search through one folder while summing rows/columns of specific cells from several workbooks to one workbook. (credit: daverunt)

Sub SumCells()
Application.ScreenUpdating = False
'Display Open Dialog to select file directory
filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
"*.xls*", 1, "Select Files", "Open", False)
 
'If the user cancels file selection then exit
If TypeName(filenames) = "Boolean" Then
Exit Sub
End If
 
'Set xls as SourceFile
SourceFile = Dir("*.xls*")
Do While SourceFile <> ""
 
If Not (SourceFile) = "Master Summary.xlsm" Then
Workbooks.Open (SourceFile)
Set XLSFile = ActiveWorkbook
Worksheets("Summary").Select
    Set Rng = Sheets("Summary").Range("H5:J11,B28:B31,F28:F30,F33:F34,J35:J35,B41:B44,B50:B57,G41:G46,G50:G60,J61:J61")
    For Each Cell In Rng
 
    x = Cell.Address(0, 0)
    CurrValue = Cell.Value
 
 ThisWorkbook.Activate
 Worksheets("Summary").Select
 
    TotalValue = Range(x).Value
    Range(x).Value = TotalValue + CurrValue
 
 Next
 Windows.Application.CutCopyMode = False
 XLSFile.Close False
 
 End If
 SourceFile = Dir
  Loop
Application.ScreenUpdating = True
End Sub