Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add Cells Using Multiple Files in One Folder
I have a folder with about 20 excel files in it that I want to add all
numbers in A1 of the 20 files to a new excel file. What is the best way to do this? I have a bunch of cells I want to do this with. Thank you Jeremy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add Cells Using Multiple Files in One Folder
With 5 or less files, I would use a single formula. With more, I would use a
helper column: In A1 thru A20 enter formulas like: ='C:\Temp\[visit me.xls]Sheet1'!$A$1 ='C:\Temp\[second.xls]Sheet1'!$A$1 ='C:\Temp\[third.xls]Sheet1'!$A$1 and then =SUM(A1:A20) -- Gary''s Student - gsnu200795 "Jeremy" wrote: I have a folder with about 20 excel files in it that I want to add all numbers in A1 of the 20 files to a new excel file. What is the best way to do this? I have a bunch of cells I want to do this with. Thank you Jeremy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add Cells Using Multiple Files in One Folder
Sub gettotals()
Folder = "C:\temp\" SumCells = Array("A1", "B2", "C3") 'open newbook Set newbk = Workbooks.Add Set newsht = newbk.Sheets("Sheet1") newsht.Name = "Summary" GoTo mend FName = Dir(Folder & "*.xls") Do While FName < "" Set OldBk = Workbooks.Open(Filename:=Folder & FName) Set OldSht = OldBk.Sheets("Sheet1") For Each cell In SumCells If IsNumeric(OldSht.Range(cell).Value) Then newsht.Range(cell).Value = newsht.Range(cell).Value + _ OldSht.Range(cell).Value End If Next cell OldBk.Close savechanges:=False FName = Dir() Loop mend: fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls), *.xls") newbk.SaveAs Filename:=fileSaveName ActiveWorkbook.Close savechanges:=True End Sub "Jeremy" wrote: I have a folder with about 20 excel files in it that I want to add all numbers in A1 of the 20 files to a new excel file. What is the best way to do this? I have a bunch of cells I want to do this with. Thank you Jeremy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking certain cells from Multiple files in a folder onto a summa | Excel Worksheet Functions | |||
Extract cell data from multiple files in one folder | Excel Discussion (Misc queries) | |||
Macro help - Moving 2 cells from 100 separate files into new folder | Excel Discussion (Misc queries) | |||
Copying Cells from CSV files in folder into one worksheet | Excel Discussion (Misc queries) | |||
Linking cells to files in a folder | New Users to Excel |