Hello all,

I want to get the average from each column of one sheet and "writing" it to the file where my macro is. Then below it, the one of the next file and so on...

Attached is my code so far. Unfortunately it's not working. Could you suggest me anything?

Thank you in advance


Sub Rectangle1_Click()

    Dim folder As String, fileName As String
    Dim wb As Workbook
    Dim i As Integer
    Dim j As Integer


    folder = "FOLDER HERE"   'CHANGE AS REQUIRED
    m = 1

    If Right(folder, 1) <> "\" Then folder = folder & "\"
    fileName = Dir(folder & "*.xls*")
    While fileName <> ""
        Set wb = Workbooks.Open(fileName:=folder & fileName)
        For i = 2 To wb.Worksheets.Count
            m = m + 1
            Sheet1.Cells(m + 2, 2) = wb.Worksheets(i).Name
            For j = 1 To 7
                Average = Application.WorksheetFunction.Average(wb.Worksheets(i).Columns(j))
                
                With Workbooks("Pier-Data.xlsm")
                    .Worksheets(1).Cells(1, 1) = i
                    .Worksheets(1).Cells(m + 2, j + 1) = Average
                    .Worksheets(1).Cells(m + 2, j + 2) = Average
                    .Worksheets(1).Cells(m + 2, j + 3) = Average
                    .Worksheets(1).Cells(m + 2, j + 4) = Average
                    .Worksheets(1).Cells(m + 2, j + 5) = Average
                    .Worksheets(1).Cells(m + 2, j + 6) = Average
                    .Worksheets(1).Cells(m + 2, j + 7) = Average
                End With
            Next
        Next

        wb.Close savechanges:=True
        fileName = Dir()
    Wend
End Sub