One step workbook consolidation and calculation?
Posted by Russ Crawford on August 10, 2001 1:42 PM
Have 500 lines by 60 cols of hours per project worked
in Excel 2000. Need to receive 20 separate workbooks
(one per employee) and obtain the sum of hours per
project etc...employees use the same fields in their
workbooks to code hours for particualr projects. Would
like to merge/consolidate/overlay each workbook into
my master but sum the various fields during this step.
Is this possible to set up? thanks!
Posted by Damon Ostrander on August 11, 2001 4:02 PM
Russ,
Yes, this is easy to do with VBA. I would recommend using a loop with a Dir function in it to automatically open each workbook and add the values to your master, then close it and open the next.
To add the values to the master, assuming the data are in the same rows and columns, just create a loop that looks like:
dim WBM As Worksheet
Set WBM = Workbooks("Master").Worksheets(1)
For iRow = 1 To 500
For iCol = 1 to 60
WBM.Cells(iRow,iCol) = Activesheet.Cells(iRow,iCol) + WBM.Cells(iRow,iCol)
Next iCol
Next iRow
where the destination sheet is the active worksheet since presumably it has just been opened.
Happy computing.
Damon
