calculate totals from added sheets

[des-sa]

pls help again
i have a petty cash file, where i open a new sheet for every day from a
template sheet. how do i keep running totals of 10 data ranges in every
sheet in one sheet - bearing in mind that every day a new sheet gets added?
thanks

 

[Bernard Liengme]

Before you first daily sheet add a blank sheet and call it MyFirst
After you last daily sheet add a blank sheet and call it MyLast
Always add new sheets in front of MyLast
On the summary sheet (that should be outside the MyFirst:MyLast group use
the formula
=MyFirst:MyLast!A20
to add all the A20 cells in all the daily sheets
best wishes

 

[des-sa]

bernard,
thanks for reply. i did exactly as you said: named 2 sheets "MyFirst" and
"MyLast", inserted a new sheet "Sum" right at the beginning, before
"MyFirst". left sheet "MASTER" in the middle of "MyFirst" & "MyLast".
entered the formula you gave in cell A2 of sheet "Sum" (i changed cell A20 in
your formula to B18 - which is my relevant one. the return i get is "#REF!".
where have i gone wrong?
thanks

 

[John C]

Your sheets should be set up in the order as follows:

Master MyFirst Sheet1 Sheet2 Sheet3 ... Sheet10 MyLast

Then on the Master sheet, where you want the total to appear, you would type
the following formula:
=SUM(MyFirst:MyLast!$B$18)

 

[Bernard Liengme]

Thanks, John. I left out SUM
The 34C (95F0 temp is getting to me here in the far north
best wishes

 

[des-sa]

john,
thanks for your reply. sheet "master" is actually the template that i use
to copy as the new day's sheet with a macro i created. roughly explained,
the macro opens a copy of sheet "master", clears the entries in the previous
day's cell ranges where data have been entered into (not the cells where
totals are calculated). how do i force the opening of the new sheet every
day to be after the previous day's sheet? or can you may suggest a simpler
way of doing it?
thanks for your patience with me

 

[John C]

This should be part of your macrocode:
Sheets("Master").Select

The line after that, you would have the following:
Sheets("Master").Copy Before:=Sheets("MyLast")

 

[des-sa]

JOHN & BERNARD
I GOT SO CHUFFEDLY MIXED UP IN MY WORK I FORGOT TO THANK YOU GUYS. THANK
YOU SO MUCH GUYS
DISRI