CODEWITHSUNDEEP
excelvba
lipesavaris
lipesavaris

Reputation: 5

Excel Sum across multiple sheets

I'm new in VBA and hope someone can help me with this. I have create a custom function to sum the value of a cell from multiple sheets. However the cell in the sheet that I have to use the function is not the same that I need to sum. For example I need to sum B2 from all sheets but the place where I need to use the function is the cell C2. Could someone help? My code:

Function AutoSum() As Variant
    AutoSum = 0
    For Each ws In Worksheets
        If Not ws Is Application.ThisCell.Parent Then AutoSum = AutoSum + ws.Range(Application.ThisCell.Address)
    Next
End Function

Upvotes: 0

Views: 569

Answers (1)

Davesexcel
Davesexcel

Reputation: 6982

You can change it slightly and place the range you want to sum

=autosum(d1) will sum all the D1 cells.

Function AutoSum(rng As Range) As Variant
    AutoSum = 0
    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

Upvotes: 1

Related Questions