How do I create a running total from all preceeding sheets?

[Tubes2015]

First Post!!

Hi everyone,
I'm pretty new to vba and this is the first workbook where I have had to properly apply it so please bear with me. What I am trying to do is have a running total on my last sheet that will update when a command button is clicked. The issue I have is that I will be adding sheets each day to the workbook so cannot define at which point it would stop. I currently have this, however, it only works on one sheet (the first one) and doesn't allow me to use on the subsequent sheets:

Please Login or Register  to view this content.

The first sheet is called Load and my sheets currently go from load, load (1), Load (2) .... to Load (10) with all the collected data so far simplified in a sheet called Collated Data, where I would like my cumulative total to be. I would like to add cell B53 in all the load sheets and put the running total in cell N4 in the Collated Data sheet. Any help would be much appreciated as I'm still trying to get to grips with everything.

Thank you in advance.

[Kaper]

if your Collated Data Sheet is indeed the last one, a minor modification shall do:

Please Login or Register  to view this content.

please edit your post to comply with forum rules - namely Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Tubes2015]

Thanks Kaper, I should have changed that now.

I have tried your code but that only includes sheets Load and Load (1). I had modified it like this:

Please Login or Register  to view this content.

This then includes all ten load sheets I currently have. Is there any way of doing this so that I don't have to change it each time a sheet is added (i.e. changing this to 11 when I have 11 sheets)?

[Tubes2015]

Sorry I'm not sure if I am explaining this very well. The above code produces this formula in the right cell

Please Login or Register  to view this content.

which gives the running total that I am looking for. However, it only works up to sheet Load (10), is there a way this can increase every time I add another sheet rather than having to do it manually?

Thanks in advance

[JOHN H. DAVIS]

Would this approach work for you?

Please Login or Register  to view this content.

[Tubes2015]

That works perfectly, thank you very much John H. Davis much appreciated.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please mark this thread as solved.