PDA

View Full Version : sum of different cells in 20 different workbook-sheets



miatch
07-22-2009, 02:42 AM
I need help making a vba-code that takes the sum of different cells in 20 different workbook-sheets and put the total(sum) in a seperate worksheet.The name tabs on the sheets are going to change from time to time and I need the code to register the sum every day (00.00pm), and for each day put the new sum in a new row in the new worksheet.
Can somebody please help me with this?

thanks

Oorang
08-14-2009, 08:59 AM
Sure:) How do you identify which workbooks should be summed (example: everything in one folder, everything in a tree, with a certain name structure, etc.)?
How do you know which worksheets, if the name changes? Will there be more than one sheet? Will the names be similar, the tabs have the same color, have a title somewhere on the sheet?
Finally is the cell to be summed in the same location on everysheet?

Bob Phillips
08-15-2009, 05:14 AM
Adapt this to suit



Const Summary_Sheet As String = "Master" '<<<<< change to suit
Dim sh As Worksheet
Dim tmp As Double

For Each sh In ActiveWorkbook.Worksheets

If sh.Name <> Summary_Sheet Then

tmp = tmp + Application.Sum(sh, UsedRange)
End If
Next sh

With Worksheets(Summary_Sheet).Range("A1").End(xlDown).Offset(1, 0)

.Value = formta(Date, "dd mmm yyyy")
.Offset(0, 1).Value = tmp
End With