Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for adding a cell in multiple workbooks
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for adding a cell in multiple workbooks
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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a Row to Multiple Workbooks | Excel Discussion (Misc queries) | |||
Adding multiple workbooks together | Excel Discussion (Misc queries) | |||
adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions | |||
Adding cells from multiple workbooks | Excel Discussion (Misc queries) | |||
Re What is the formula for adding multiple numbers in a cell | Excel Discussion (Misc queries) |