I would like to create a macro that adds up the sheets of similar excel
workbooks and put the results in a new workbook.

I have a folder that contains the workbooks all with similar names for
example m0507100.xls (for may 2007 and 100 is company identifier). Each of
these workbooks have the same number of sheets and same sheet names.

I want to add the values of all respective cells in all workbooks in the
folder and put them in a new workbook with identical sheet names as the
workbooks that are added up.

I'm new to programming so I would appreciate any help with this problem. I
use excel 2003.

I didn't test the code but is probably will work. The code assumes that every
row and column contains numerical data that need to be added and there are no
blank cells. The code starts at cell A1 on each sheet and stops when it gets
an empty cell in column A. Likewise for each row it starts in Column A and
continues until it finds an empty cell.

The code creates a new workbook and then adds new worksheets to match the
sheets in the old workbooks.


Sub get_totals()

Folder = "C:\Temp"

Set Total_bk = Workbooks.Add

FName = Dir(Folder & "\*.xls")
First = True
Do While FName < ""
Set old_bk = Workbooks.Open(Filename:=Folder & "\" & FName)
With old_bk
For Each sht In old_bk
If First = True Then
With Total_bk
.Sheets.Add after:=.Sheets(.Sheets.Count)
total_bk.Activesheet.name = sht.name
End With
End If
RowCount = 1
Do While .Range("A" & RowCount) < ""
ColCount = 1
Do While .Cells(RowCount, ColCount) < ""
Total_bk.Sheets(sht.Name).Cells(RowCount, ColCount) = _
Total_bk.Sheets(sht.Name).Cells(RowCount, ColCount) + _
sht.Cells(RowCount, ColCount)

ColCount = ColCount + 1
Loop
RowCount = RowCount + 1
Loop
Next sht
End With
old_bk.Close Savechanges:=False
First = False
FName = Dir()
Loop
Total_bk.Close

End Sub


"agkistras" wrote:

I would like to create a macro that adds up the sheets of similar excel
workbooks and put the results in a new workbook.

I have a folder that contains the workbooks all with similar names for
example m0507100.xls (for may 2007 and 100 is company identifier). Each of
these workbooks have the same number of sheets and same sheet names.

I want to add the values of all respective cells in all workbooks in the
folder and put them in a new workbook with identical sheet names as the
workbooks that are added up.

I'm new to programming so I would appreciate any help with this problem. I
use excel 2003.

Thanks a lot for your help!

When I run the code i get a run-time error... object doesn't support this
property or method.... and when I debug it it highlights the following:

For Each sht In old_bk

Any ideas why i get this error?

"Joel" wrote:

I didn't test the code but is probably will work. The code assumes that every
row and column contains numerical data that need to be added and there are no
blank cells. The code starts at cell A1 on each sheet and stops when it gets
an empty cell in column A. Likewise for each row it starts in Column A and
continues until it finds an empty cell.

The code creates a new workbook and then adds new worksheets to match the
sheets in the old workbooks.


Sub get_totals()

Folder = "C:\Temp"

Set Total_bk = Workbooks.Add

FName = Dir(Folder & "\*.xls")
First = True
Do While FName < ""
Set old_bk = Workbooks.Open(Filename:=Folder & "\" & FName)
With old_bk
For Each sht In old_bk
If First = True Then
With Total_bk
.Sheets.Add after:=.Sheets(.Sheets.Count)
total_bk.Activesheet.name = sht.name
End With
End If
RowCount = 1
Do While .Range("A" & RowCount) < ""
ColCount = 1
Do While .Cells(RowCount, ColCount) < ""
Total_bk.Sheets(sht.Name).Cells(RowCount, ColCount) = _
Total_bk.Sheets(sht.Name).Cells(RowCount, ColCount) + _
sht.Cells(RowCount, ColCount)

ColCount = ColCount + 1
Loop
RowCount = RowCount + 1
Loop
Next sht
End With
old_bk.Close Savechanges:=False
First = False
FName = Dir()
Loop
Total_bk.Close

End Sub


"agkistras" wrote:

I would like to create a macro that adds up the sheets of similar excel
workbooks and put the results in a new workbook.

I have a folder that contains the workbooks all with similar names for
example m0507100.xls (for may 2007 and 100 is company identifier). Each of
these workbooks have the same number of sheets and same sheet names.

I want to add the values of all respective cells in all workbooks in the
folder and put them in a new workbook with identical sheet names as the
workbooks that are added up.

I'm new to programming so I would appreciate any help with this problem. I
use excel 2003.

from
For Each sht In old_bk
to
For Each sht In old_bk.sheets

"agkistras" wrote:

Thanks a lot for your help!

When I run the code i get a run-time error... object doesn't support this
property or method.... and when I debug it it highlights the following:

For Each sht In old_bk

Any ideas why i get this error?

"Joel" wrote:

I didn't test the code but is probably will work. The code assumes that every
row and column contains numerical data that need to be added and there are no
blank cells. The code starts at cell A1 on each sheet and stops when it gets
an empty cell in column A. Likewise for each row it starts in Column A and
continues until it finds an empty cell.

The code creates a new workbook and then adds new worksheets to match the
sheets in the old workbooks.


Sub get_totals()

Folder = "C:\Temp"

Set Total_bk = Workbooks.Add

FName = Dir(Folder & "\*.xls")
First = True
Do While FName < ""
Set old_bk = Workbooks.Open(Filename:=Folder & "\" & FName)
With old_bk
For Each sht In old_bk
If First = True Then
With Total_bk
.Sheets.Add after:=.Sheets(.Sheets.Count)
total_bk.Activesheet.name = sht.name
End With
End If
RowCount = 1
Do While .Range("A" & RowCount) < ""
ColCount = 1
Do While .Cells(RowCount, ColCount) < ""
Total_bk.Sheets(sht.Name).Cells(RowCount, ColCount) = _
Total_bk.Sheets(sht.Name).Cells(RowCount, ColCount) + _
sht.Cells(RowCount, ColCount)

ColCount = ColCount + 1
Loop
RowCount = RowCount + 1
Loop
Next sht
End With
old_bk.Close Savechanges:=False
First = False
FName = Dir()
Loop
Total_bk.Close

End Sub


"agkistras" wrote:

I would like to create a macro that adds up the sheets of similar excel
workbooks and put the results in a new workbook.

I have a folder that contains the workbooks all with similar names for
example m0507100.xls (for may 2007 and 100 is company identifier). Each of
these workbooks have the same number of sheets and same sheet names.

I want to add the values of all respective cells in all workbooks in the
folder and put them in a new workbook with identical sheet names as the
workbooks that are added up.

I'm new to programming so I would appreciate any help with this problem. I
use excel 2003.