Adding from next sheet to the end sheet

Question

Assume I have n sheets. My second sheet is called "Calc", which is where I do my summation/calculation.

I'd like to add everything A1, A2... A1000, to Z1, Z2...Z1000 from sheet 3 (sheet after Calc) to sheet n.

These are imported sheets. I do not know the name of these sheets and I am not allowed to change them.

Any of the sheets between and including sheet 3 to sheet n can be removed or added at any time.

First I was thinking of trying =SUM(''!A20), but it automatically changes the '' to the first and last sheet.

When I remove the last sheet, it gives me error and the calculation fails. I was thinking of doing indirect, but it would be very tedious, as I cannot drag to change the cells in sheet 3 to sheet n.

for example: =SUM(INDIRECT("'"&F2&"'!C4"),INDIRECT("'"&F3&"'!C4")), the C4 does not change as I drag them across the board.

Any other idea?

An idea - if I understand correctly - is what if on your "Calc" sheet, row 1 lists all the sheet names in the workbook. Then, under that, you can do a SUM() formula with indirect pointing to A1, B1, etc. to get the sheet name. Then drag that across? — BruceWayne
This is tagged VBA but you're aiming for a non-VBA solution? — Greg
That was my first idea. However, the cells in the indirect function that refers to another sheet doesn't change as you drag. Above, the C4 doesn't change. So if I have 1000 cells, then I'd have to change for each one of them. — Seven
I would rather not, unless there are no solution without VBA. Then that option is better than no option — Seven
@Seven - if you type that formula in, and drag, the "C4" does NOT change? You're not using VBA to add that formula, or are you? — BruceWayne

Andy Andy · Accepted Answer · 2015-06-26T03:27:40

Let me guess:

You have many sheets;
You can delete or add new sheet;
There are data in Range("A1:Z1000") in every sheet;
You want sum every sheet's Range("A1:Z1000") in sheet("Calc").Range("A1:Z1000");

Every sheet has data in A1:Z1000. enter image description here

Sum in "Calc" sheet. enter image description here

If so try this:

Sub SumEverySheet()

Dim Sh As Worksheet
Dim i, j As Integer
Dim cellValue

For Each Sh In Worksheets
    If Sh.Name <> "Sheet1" And Sh.Name <> "Calc" Then
        ' row: 1 to 1000
        For i = 1 To 1000
            ' A - Z (1 - 26)
            For j = 1 To 26
                cellValue = Sheets("Calc").Cells(i, j)
                sheetValue = Sh.Cells(i, j)
                Sheets("Calc").Cells(i, j) = sheetValue + cellValue
            Next j
        Next i
    End If
Next Sh
End Sub

Put this macro in Workbook_AfterSave can make it run automatically after save.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    MsgBox "after save"
    Call SumEverySheet
End Sub

Hope this will help you.

Adding from next sheet to the end sheet

1 Answers