Dynamic Sum's

  • Thread starter Thread starter Bobby
  • Start date Start date
B

Bobby

I have a work book where I allow the user to insert new workshees,
basically it is just a simple recored macro where I have a hidden
balnk template and just insert new, unhide copy and paste and hide
blank template again. Here is the question, I have a summary sheet
where I look at running totals of all the sheets commined, how do I
rework the formula to get the sums of all A1 in the work book with the
exception of the current sheet (summary sheet), the foemula does not
take into consideration new sheets obviously?

Thanks
 
How is your workbook structured? Are all sheets the same, although if you are
only looking to get A1 summed I guess it doesn't really matter.

Set your workbook up so that it has a summary sheet as the first sheet, a dummy
sheet titled 'a' as the second, as many intermediate sheets as necessary, and
then finally a sheet titled 'z'.

Have your summary sheet pull all data from a:z, eg =SUM(a:z!A1) for cell A1.
You can put that formula anywhere you like on that sheet if you wish, but a
Summary sheet that looks the same as your intermediate sheets will be easier to
follow.

Now if you like you can simply hide a and z, or even leave them there. Then
have your macro do eaxactly as before, but simply ensure that it puts the new
sheet before sheet z, and it will then automatically get pulled into the total,
eg you could even set up Sheet z to be your template and then use something
like:-

Sub CreateNewSheet()
Sheets("z").Copy Before:=Sheets("z")
End Sub

If you have 2002 or later, then colour the tabs of a and z as bright red to mark
your start and end points. If you want to hide a and z then just be sure that
your code leaves the new sheet visible - More than one way of doing this, but:-

Sub CreateNewSheet()
Application.Screenupdating = False
Sheets("z").Visible = True
Sheets("z").Copy Before:=Sheets("z")
Sheets("z").Visible = False
Application.Screenupdating = True
End Sub
 
Insert your sheets between existing sheets instead of at the front/back.
Have a formula in each sheet that has the sums for that sheet. In the
summary sheet =sheet2:sheet12!a1
 
Back
Top