This will open the workbook, you can set it to a variable to deal with it easier.
Do an array, loop through it, opening each workbook and worksheet into a named range of values.
not actual code thought puke:
DIM WB as workbook
DIM WS as worksheet
DIM SrcName as String
DIM array: some array as range type
for i loop
SrcName = "C:\Users\Me\This" + i +".xlsx"
WB = Workbooks.Open(FileName:=SrcName, ReadOnly:=True)
WS = WB.Worksheets("name of the tab they all filled in")
array[i] = WB.WS.Range("A1:Z99") or whatever the range of data is needed.
Once the array is filled with range stuff, then you can "capture" any field and sum it with loops:
for i loop
Sum1 = Sum1 + array[i].Range("B4").Value
ThisWB.ThisWS.ThisRange.Value = Sum1
The idea here is:
1. We open each workbook, look at each statically named worksheet, and capture the same RANGE in all of them.
2. We use these RANGE variables to extract .Value
3. We run this in the master workbook, worksheet; setting the value here to be the sum from the previously opened workbooks and worksheets that we extracted RANGE data from.
It won't be fast, could puke often, and should have MANY On Error handlers: but is KISS.--
Show off that hardware: join Team Discovery and Team Helix