Copy particular columns from one file to master file with condition

[mana]

Option Explicit

Sub test()
    Dim a As Object
    Dim p As String, f As String
    Dim v
    Dim i As Long
    Set a = CreateObject("system.collections.arraylist")
    p = ThisWorkbook.path & "\"
    f = Dir(p & "*.xlsx")
    Do While f <> ""
        With Workbooks.Open(p & f, ReadOnly:=True)
            v =.Sheets(1).Cells(1).CurrentRegion.Value
            For i = 2 To UBound(v)
                a.Add Array(f, IIf(v(i, 3) = "M", v(i, 3), v(i, 4)), v(i, 5), v(i, 6))
            Next
            .Close
        End With
        f = Dir()
    Loop
    With ThisWorkbook.Sheets(1)
        .UsedRange.Offset(2).ClearContents
        .Cells(3, 1).Resize(a.Count, 4).Value = _
            Application.Transpose(Application.Transpose(a.toarray))
    End With
 End Sub

マナ