Summing the same cell on sheets after this one

[Rag Tag Army]

Hi helpful geniuses.

I want to sum the same cell on every sheet following the sheet with the formula on it, but I can't guarantee the name or order of the sheets which follow it.

I can't simply sum from all sheets as the preceding sheet currently has data in some of the cells I want to do this with, and whilst a rebuild may be on the cards its not something I can undertake just now.

The best I am capable of achieving is bookending with blank sheets to give known names on either end, but this seems somewhat inelegant, and doesn't help with navigation or auto-indexing.

Whats the best way to go about this?

The first sheet contains a list of all the other sheets in the workbook in column A which is recompiled every time the sheet is visited if this is of any use.


Thanks in advance for any help.

 

[My Aswer Is This]

You said:

" want to sum the same cell"

My question: What cell?

 

[FDibbins]

if you add a blank worksheet at the start and end of your data sheets, and call them Start and End, then when you enter your calcs, you could just enter =sum('start:End'!C12) or whatever your references are. this way, you can add or remove sheets as needed, and it will add across all the sheets you need it to, and keep the totals as the data sheets change. make sure your "Master" sheet is before the Start sheet though

 

[My Aswer Is This]

Try this Vba script:

The script will sum all the values in Range("A1") from the active sheet to the last sheet.
And put the results into Range("A2") of the active sheet.

Sub Sum_Sheets()
Dim i As Long
Dim ans As Long
ans = ActiveSheet.Index
Dim aa As Long
    For i = ans To Sheets.Count
        aa = aa + Sheets(i).Range("A1").Value
    Next
    Sheets(ans).Range("A2").Value = aa
End Sub

 

[Rag Tag Army]

Thanks folks.

if you add a blank worksheet at the start and end of your data sheets, and call them Start and End, then when you enter your calcs, you could just enter =sum('start:End'!C12) or whatever your references are. this way, you can add or remove sheets as needed, and it will add across all the sheets you need it to, and keep the totals as the data sheets change. make sure your "Master" sheet is before the Start sheet though

Yup, I've considered this, and might use it if necessary, but would really benefit from not adding 2 extra sheets as it would both hinder tab navigation and add 2 erroneous entries to the automatically generated index which also doubles as a key part of the summary sheet.

You said:

" want to sum the same cell"

My question: What cell?

Well there will be a number of them, namely y21 through AD34, but I'm operating on the perhaps naive assumption that what I can do for one cell I can simply repeat for others.

Try this Vba script:

The script will sum all the values in Range("A1") from the active sheet to the last sheet.
And put the results into Range("A2") of the active sheet.

Sub Sum_Sheets()
Dim i As Long
Dim ans As Long
ans = ActiveSheet.Index
Dim aa As Long
    For i = ans To Sheets.Count
        aa = aa + Sheets(i).Range("A1").Value
    Next
    Sheets(ans).Range("A2").Value = aa
End Sub

Thanks. This is exactly the sort of magic I was after.

I've added it to the sheet in question, and doubtless something I've done wrong, the target cell remains blank. VBA in other sheets is working fine.

Any ideas what I'm doing wrong?

Thanks again.

 

[My Aswer Is This]

Not sure what this means:
"I've added it to the sheet in question"

This is a module script. It is not a Autosheet event script.


Not sure what this means:

VBA in other sheets is working fine.

Do you mean you have other scripts not mine in other sheets?

 

[Rag Tag Army]

Not sure what this means:
"I've added it to the sheet in question"

This is a module script. It is not a Autosheet event script.


Not sure what this means:

VBA in other sheets is working fine.

Do you mean you have other scripts not mine in other sheets?

Ah ok...

Sorry I seem to have misdefined the problem slightly.

I need the summing to occur on a specific sheet, and for it to sum the values in cell 'Y21' on every subsequent sheet, not on whichever sheet is active. The first sheet in the book is a summary. I'm adding a second summary sheet on which I want this to appear, all the other sheets are data.

I have one piece of module code which produces a last save date, and one sheet specific piece of VBA which automatically indexes the sheets in the workbook.

Sorry for the miscommunication.

 

[My Aswer Is This]

Rag Tag Don't give me information like this or helping you will take forever.

You said: "I need the summing to occur on a specific sheet,"

Give me the sheet name don't say specific sheet.

I need to know what sheets you want summed and where we want the results of this summing put.

You need to say sum sheets three to last sheet and put results into sheet named "Summary" Range ("A1")

See this is being specific and you previous said you wanted to sum more then one cell per sheet. You said:

Well there will be a number of them, namely y21 through AD34,

If that is the range then say so and do not think it will be easy to modify it later yourself.

I would tell the script to sum all sheets from sheet 3 to last sheet Range("Y21:AD34")
And put results into sheet "Summary" Range ("A1") for example.

 

[Rag Tag Army]

Sorry, knowing next to nothing about this my tendency is to break big problems down as far as I can, and then if I still can’t solve it ask for help on the little problem and scale back up, so I’d got to the point where if I knew how to make this work for a single cell I could have affected some sort of solution without straight out asking somebody to do all the work for me…

Here’s the full story:

First Sheet: Summary

Second Sheet: CT

Subsequent Sheets all use an identical format, but their number will increase over time and their order is subject to change. The blank I use to create new sheets is also in this group, and contains the same structure and formulas as the data sheets, but has no data so the cells in question will contain 0 values.

On sheet CT across cells AF9-AL22 I want to produce the sums of each cell y21-AD34 on every subsequent sheet. So cell AF9 on sheet CT contains the sum of all the Y21 cells on subsequent sheets, cell AG9 on sheet CT contains the sum of all the values in cells Z21 and so on.

Thanks once again, hope this contains all the pertinent information.

 

[My Aswer Is This]

Thank you for that explanation. This task is getting more complicated then I can help you with.

Maybe someone else here at Mr. Excel will be able to help you.

Or maybe you can modify the script I provided to your needs.
I will continue to monitor this thread to see what I can learn here.