finding average between values from different workbooks

Hello to everyone!

I need your help! I have 100 workbooks named Value_1 to Value_100.. In a specific cell W2 in sheet (named Model) in all workbooks there is some value (that comes as a formula result).
I would like to write a vba code that finds the average of these 100 values and saves it in another workbook, sheet2, cell O2..
Can anyone help me please? I tried many things, but i'm an absolute beginner and nothing works...

Thanks in advance!

Try this macro

Sub Test()
Const MyPath = "C:\temp\test\"  'Modify this to your path
Const TargetWorkbook = "C:\temp\test2\MyWorkbook.xlsx"  'Modify this to your target workbook

For N = 1 To 100
    Workbooks.Open Filename:=MyPath & "Value_" & N, ReadOnly:=True
    Total = Total + ActiveWorkbook.Sheets("Model").Range("W2")
    ActiveWorkbook.Saved = True 'Prevents any 'do you wnat to save messages'
    ActiveWorkbook.Close
Next N

Workbooks.Open Filename:=TargetWorkbook
ActiveWorkbook.Sheets("Sheet2").Range("O2") = Total / 100
End Sub

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

mrice,

thanks a lot!! you made everything easy for a beginner like me..
Thanks for the detailed explanation! My problem is solved now!