Solved: Sum Corresponding cell from different worksheet

[tlchan]

Hi there,

I tried to sum all corresponding cell of all worksheets into 1 summary. Is there any VBA to perform the task instead of worksheet function on every cell in summary sheet ?

Attached the file( Yellow range is where the sum of corresponding cell from sheet1 to sheet 3)


Thank you:think:

[nb-]

Perhaps something like this rather crude function (see attached file);

Sums the values (if they are numeric) in all cells with the same address as the address of the cell the function is written in on all the other worksheets of the active workbook.


Public Function dbl_sum_cell_in_all_other_sheets()

Dim ws As Worksheet
Dim str_caller_ws As String
Dim str_cell_address As String
Dim dbl_running_total As Double

str_cell_address = Application.Caller.Address
str_caller_ws = Application.Caller.Worksheet.Name

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> str_caller_ws Then
If IsNumeric(ws.Range(str_cell_address).Value) Then
dbl_running_total = dbl_running_total + ws.Range(str_cell_address).Value
End If
End If
Next ws


dbl_sum_cell_in_all_other_sheets = dbl_running_total

End Function

[mdmackillop]

If the data is consistently placed, you can do this by formula
=SUM(Sheet1:Sheet3!C4)

[tlchan]

Dear Md & nb,

Thank for your fast solution. Both option works fine

You are briliant.