Prev: Calculating over multiple tabs
Next: Extract the month name from a month
From: PaPr on 3 Feb 2010 14:21
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?
From: Otto Moehrbach on 3 Feb 2010 16:31
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

"PaPr" <PaPr(a)discussions.microsoft.com> wrote in message
news:72434593-6B84-4421-AA2B-BBA2BF87E332(a)microsoft.com...
> 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?

 | 
Pages: 1
Prev: Calculating over multiple tabs
Next: Extract the month name from a month