Exel macro - mean a column for all files in a directory

[zenilk]

I have around 2000 exel files in a directory. Some have filename = x + background.xls and others Filename = x + processed.xls In each one there are seven Column in the first row: Area Mean Min Max IntDen RawIntDen Bellow the first row are the values. I want to obtain the Mean of all the values in the 'Mean' (B) Column and paste this value in a new exel file (if possible one file for the results of filename = x + background.xls and other for the Filename = x + processed.xls) in the B column with the Filename that originated the value in the same row in A column.
I don't know how to make a code for this goal, can someone help me?

 

[JLGWhiz]

You did not specify a directory path, so this code assumes that the workbook hosting the code will be in the same directory as the 1000 other workbooks.

Sub realMean()
Dim wb As Workbook, wbb As Workbook, wbp As Workbook, sh As Worksheet, fName As String, fPath As String
fPath = ThisWorkbook.Path 'If host workbook in different directory, enter the full directory path for the target files here.
Set wbb = Workbooks.Add
Set wbp = Workbooks.Add
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do
        If InStr(fName, "background") > 0 Then
            Set wb = Workbooks.Open(fPath & fName)
            Set sh = wb.Sheets(1)
            cnt = sh.Range("B2", sh.Cells(Rows.Count, 2).End(xlUp)).Rows.Count
            m = Application.Sum(sh.Range("B2", sh.Cells(Rows.Count, 2).End(xlUp))) / cnt
            wbb.Sheets(1).Cells(Rows.Count, 1).End(xlUp)(2) = wb.Name
            wbb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = m
            wb.Close False
        ElseIf InStr(fName, "processed") > 0 Then
            Set wb = Worksbooks.Open(fPath & fName)
            Set sh = wb.Sheets(1)
            cnt = sh.Range("B2", sh.Cells(Rows.Count, 2).End(xlUp)).Rows.Count
            m = Application.Sum(sh.Range("B2", sh.Cells(Rows.Count, 2).End(xlUp))) / cnt
            wbb.Sheets(1).Cells(Rows.Count, 1).End(xlUp)(2) = wb.Name
            wbb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = m
            wb.Close False
        End If
        fName = Dir
    Loop While fName <> ""
wbb.SaveAs "background mean.xlsx" 'assuming you have upgraded to xl2007 or later.
wbp.SaveAs "processed mean.xlsx" 'if not change the file extension accordiningly.
End Sub

this code is untested except for that it will compile. You should test it on copies of your files or mock up files before applying to the original. The code should be copied to the standard code module 1 of the host workbook and it is written assuming that the host workbook will be in the same directory as the target workbooks.