Extracting data from different workbooks combine into one VBA

Hi Friends,

I am a beginner user of VBA. I want to take average of specific columns in different workbooks (average values are at the last row) and put them in another workbook. I could not do the loop it did not work. I need to take average of 3rd 4th and 5th columns. And put the results in another workbook. For the data taken from each workbook the results rows offsets one below. And 3rd column s result will be in 1st column in the mainworkbook where I want to keep the results. Every time I changed the numbers in the code and I could take data. Could you please help me about the code below. THANKS

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("...")

Do While Len(MyFile) > 0
If MyFile = "results1.xlsm" Then
Exit Sub
End If

Workbooks.Open (MyFile)

Worksheets("Time series").Cells(1, 5).End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Average(Worksheets("Time series").Columns(5))
Worksheets("Time series").Cells(1, 5).End(xlDown).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 3), Cells(erow, 3))
MyFile = Dir
Loop
Application.DisplayAlerts = True

End Sub