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

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

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