How to sum all sheets automatically using excel formua

0
votes

I have workbook contain multiple worksheets. I want a function which display the autosum of the current worksheet as well as previous not all. For example If I am on the sheet1 it and it has the value in the cell "E12" = 1 then I will write the function =autosum(E12) which will display result in E13 = 1. Then in the sheet2 if the same cell range E12 = 2 and I applied the function "=autosum(e12) it should display on the sheet2 E13 = 3. But when I go back to the sheet1 it should be displayed same as 1 not should display total for all.

I am using this function but it display same total on all the sheet. The function is as under.

    Function AutoSum(rng As Range) As Variant
    AutoSum = ActiveSheet.Range("E12").Value
    Application.Volatile True
    For Each WS In Worksheets
        If Not WS Is Application.ThisCell.Parent Then
            AutoSum = AutoSum + WS.Range(rng.Address)
        End If
    Next
End Function

The sheets layout is as under.

Sheet1

enter image description here

2
excelvbasum

2 Answers

1
votes

Consider:

Public Function AutoSum(rng As Range) As Variant
    Application.Volatile
    Dim addy As String, ws As Worksheet, wName As String
    Dim wb As Workbook
    
    addy = rng.Address
    wName = rng.Parent.Name
    Set wb = rng.Parent.Parent
    AutoSum = 0
    
    For Each ws In wb.Sheets
        AutoSum = AutoSum + ws.Range(addy)
        If ws.Name = wName Then Exit Function
    Next ws
    
End Function

This will sum the given cell from all sheets up to and including the sheet containing the formula.

0
votes

your all code is correct only sum little changes require.

AutoSum = ActiveSheet.Range("E12").Value

To

AutoSum = rng.Value

or follow youtube video lin to use below code

https://youtu.be/4AWt7C2Ugsk

Function AutoSum(rng As Range)
Application.Volatile True
For Each WS In Worksheets
        AutoSum = WorksheetFunction.Sum(AutoSum, WS.Range(rng.Address))
Next

End Function