Sum specific cell from 'First' sheet to current sheet

Question

I want to sum the value of cells G36:G41 across all sheets between the first sheet in the book, named 'First', and the current sheet. I want to reference the current sheet in the formula using the name of the sheet. For example, if the name of the sheet I'm working on was "Example", I want a formula that will essentially give me =SUM(First:Example!G36:G41) without having to retype the name of the sheet into the formula on all subsequent sheets.

Further clarification...let's say my sheets are named "First", "Example", "Practice", and "Test"

I want cell G44 in Example to sum FirstG36:G41 + ExampleG36:G41

I want cell G44 in Practice to sum FirstG36:G41 + ExampleG36:G41 + PracticeG36:G41

I want cell G44 in Test to sum FirstG36:G41 + ExampleG36:G41 + PracticeG36:G41 + TestG36:G41.

I have tried using a nested for loop in vba:

Public Sub autoFormula()
For i = 2 To Worksheets.Count
    ReDim arr(1 To i)
    For j = 1 To i
        arr(j) = "'" & Worksheets(j).Name & "'!G36:G41"
    Next
    Worksheets(i).Range("G44").Formula2 = "=" & Join(arr, "+")
Next
End Sub

It does not add the cells into one value. It outputs: =first!G36:G41+'sheet2!G36:G41+'sheet3'!G36:G41

So it is listing the total of each cell instead of adding them together.

can someone tell me why my loop isn't doing what I am explaining?

Use `.Formula2` instead of `.Formula`. – BigBen Nov 18 '21 at 21:43 — BigBen, Nov 18 '21 at 21:43
Use `SUM` to sum the ranges together. – BigBen Nov 19 '21 at 00:00 — BigBen, Nov 19 '21 at 00:00
@BigBen, thank you, that worked! – Rosebud Nov 19 '21 at 02:01 — Rosebud, Nov 19 '21 at 02:01

Sum specific cell from 'First' sheet to current sheet

0 Answers0