Formula for adding a cell in multiple workbooks

[PaPr]

I want to add a cell from 52 different workbooks into another "Year End"
workbook. Is there a formula that makes this easier than having to open all
52 workbooks at one time and going into each to select that cell for the
formula?

 

[Otto Moehrbach]

Place all 52 files and the "Year End" file into one folder with NO other
files. Place this macro into the "Year End" file and run it FROM THAT
FOLDER. This macro will open each file and will sum the value of cell A1 in
sheet "SheetName" from every file. The result will be displayed in a
message box at the end. If you want the sum placed in the "Year End" file
in A1 of the active sheet, replace the Msgbox line in this code with:
Range("A1")=TheSum
HTH Otto

Sub AllFolderFiles()
Dim wb As Workbook, TheFile As String
Dim MyPath As String, TheSum As Double
TheSum = 0
MyPath = ThisWorkbook.Path
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile <> ""
If TheFile <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
The Sum = TheSum + Sheets("SheetName").Range("A1")
wb.Close
End If
TheFile = Dir
Loop
MsgBox TheSum
End Sub

 

[Everett P]

How would this be modified to add up the same cell in every sheet in every workbook in a file and return a sum?

 

[Everett P]

Can this be modified to add up the same cell in every sheet in every workbook in a file?