I ended up using Ingolf's solution because it was the first one I got and it
worked effortlessly. I am going to try your solution as well, if for
nothing else than to learn more. I appreciate you taking the time to post
such a detailed reply. Thanks again for the help. Will let you know how
your solution helped and what learning I took from it

Wally Steadman

"strive4peace" <"strive4peace2006 at yahoo dot com" wrote in message
...
Hi Wally,

here is some code you can modify:

'~~~~~~~~~~~
Sub AddCellFromEveryWorkbook()

'strive4peace2007 at yahoo.com

On Error GoTo Proc_Err

Dim mPath As String, mMask As String, mFile As String
Dim mCellAddr As String, mTotal As Double
Dim WB As Workbook, WBactive As Workbook
Dim mBooUpdateLinks As Boolean

mPath = "C:\path\"
mMask = "USER*.xls"
mBooUpdateLinks = False

mCellAddr = "B3"
mTotal = 0
Set WBactive = ActiveWorkbook

mFile = Dir(mPath & mMask)

Do While mFile < ""
Set WB = Workbooks.Open(mPath & mFile, _
mBooUpdateLinks)
mTotal = mTotal _
+ WB.ActiveSheet.Range(mCellAddr)
WB.Close False
mFile = Dir()
Loop

WBactive.ActiveSheet.Range(mCellAddr).Value = mTotal

Proc_Exit:
On Error Resume Next

WB.Close False
Set WB = Nothing

MsgBox "Done"
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " AddCellFromEveryWorkbook"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Sub
'~~~~~~~~~~~~~~~~~`

substituting the array method of addressing cells so you can more easily
set up a loop with the cell addresses ...

'~~~~~~~~~~~~~~~~~~~~
Sub AddCellFromEveryWorkbook()

'strive4peace2007 at yahoo.com

On Error GoTo Proc_Err

Dim mPath As String, mMask As String, mFile As String
Dim mRow As Long, mcol As Long, mTotal As Double
Dim WB As Workbook, WBactive As Workbook
Dim mBooUpdateLinks As Boolean

mPath = "C:\path\"
mMask = "USER*.xls"
mBooUpdateLinks = False

'B3 is row 3 column 2
mRow = 3
mcol = 2

mTotal = 0
Set WBactive = ActiveWorkbook

mFile = Dir(mPath & mMask)

Do While mFile < ""
Set WB = Workbooks.Open(mPath & mFile, _
mBooUpdateLinks)
mTotal = mTotal _
+ WB.ActiveSheet.Cells(mRow, mcol)
WB.Close False
mFile = Dir()
Loop

WBactive.ActiveSheet.Cells(mRow, mcol).Value = mTotal

Proc_Exit:
On Error Resume Next

WB.Close False
Set WB = Nothing

MsgBox "Done"
Exit Sub

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " CalcOrderQtys"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~


there is no error checking here to make sure that the cell you are adding
up is numbers...


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Wally Steadman wrote:
Greetings all,
Sorry for posting this again. I am not sure I am explaining my
situation clearly and have looked through my Excel 2002 Powerprogramming
with VBA and I am still lost.

Let me explain what I am trying to accomplish and then give some detailed
examples.

Employee's must fill out a sheet weekly on their activities. once they
fill out their sheets, totals are calculated on another sheet (TOTALS) in
their workbook in a range of cells from
B2:P27.

We are trying to compile their information into one master worksheet.
All workbooks are exactly the same format.

I am trying to create a sub that will start in cell B2 of the master and
sum cell B2 from all the other workbooks. Then it will move to cell B3
and sum all Cell B3's from workbooks in the folder then it will go to B4,
then B5, through P27 summing up cells in the USER##.xls workbooks in the
folder

All workbooks are named USER##.xls, where ## is the employee's user
number.

Is there a way to have it look at all files in the folder that begin with
USER?

Each week there can be a different number of user files in the folder.
Some employees may be on vacation, and more employees may be hired so I
am trying for something that is flexible enough to catch those
situations.

I am thinking I need to have variables that list the R1C1 notation and
then when it is in a cell, it will open all USER##.xls workbooks, one by
one, adding their value to a variable and when it gets to the last USER
File, it will go to the next cell and do the same thing all over again
until it finishes with cell P27.

Example of 1st Weeks files in the folder
master.xls
User1.xls
User2.xls
User3.xls
User7.xls
User8.xls

Example of 2nd week files in the folder
master.xls
User1.xls
User3.xls
User5.xls
User6.xls
User7.xls
User9.xls
User10.xls

If someone could help I would certainly appreciate it. It has been a
while since I did any VBA and while I have an idea of what I need to do,
My mind is not letting me even start.

Thanks in Advance

Wally Steadman