Sum columns in multiple files

[Viacheslav]

Hey guys,

So im trying to write and edit a macro to do the following.
I give it a folder path > it goes through all the excel files in that folder and gives me the file name and 1) number of rows in it 2) sum of columns B and D.

So all the files have similiar structure and headers. i.e for sum of columns it needs to start from B2,D2
The problem im having is adding the column summing into the loop. Im not really sure how to.

Please take a look at my code below, thanks everyone in advance!

Sub CollectData()

    Dim fso As Object, xlFile As Object
    Dim sFolder$
    Dim r&, j&
    Dim X as Range 
    Dim Y as Range

    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.Path
        If .Show Then sFolder = .SelectedItems(1) Else Exit Sub
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each xlFile In fso.GetFolder(sFolder).Files
        With Workbooks.Open(xlFile.Path)
            With .Sheets(1)
                j = .Cells(.Rows.Count, 1).End(xlUp).Row
                x = Application.WorksheetFunction.Sum(Range("B2") .End(xlDown))
y = Application.WorksheetFunction.Sum(Range("D2") .End(xlDown))
            End With
            .Close False
        End With
        r = r + 1
        Cells(r, 1).Value = xlFile.Name
        Cells(r, 2).Value = j
        Cells(r, 3).Value = x
        Cells(r, 4).Value = y

    Next

End Sub