Macro to create sum formula across all sheets except one activated in

Hello all

Let me give some background. I have a summary tab that will be in about 200 workbooks. The summary sheet is independent in each workbook but all tabs are exactly the same throughout all sheets and all workbooks..

I am looking for a macro that will create a formula that sums across all the tabs in a single workbook. Each summary page has 6000 cells that need to be summed and the remaining cells need to be untouched since there will be formulas sitting in the summary tab that is based of the summed numbers.

e.g. for one workbook that has 3 tabs SRC RCS and CRS and Summary, I want a macro that will put =sum('SRS'C!20!+'RCS'C!20!+'CRS'C!20!) in C20 of the summary tab. I want the macro to work as a range so that it will create this sum formula for C20:Q20 and create the formula for the corresponding cell in the rest of the sheets.

Is this possible? I've been racking my head and can't think of anything.

Why do you need a macro for this?
Is the formula changing all the time?
Do you just want to "punch" a formula through to all sheets?
Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Hi SAFC,

You can use 3D formulas in this case - the following will sum cell C20 from sheet SRC to CRS including all the sheets between (sheet RCS in this case):

=SUM(SRC:CRS!C20)

HTH

Robert

Just change my 3D formula above to suit.

Note for 3D formulas it's vital how the sheets are ordered for them to produce the results you want - i.e. have the Summary sheet as the first (furthest left) tab followed by the first tab you wish to sum from to the last (furthest right) tab you want to sum to.

Originally Posted by SAFC

The number of sheets and the name of the sheets change between each workbook. Sorry I meant to clarify that.

Doesnt necessarily matter.
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

Yes, and it may be worth protecting those sheets as if they end up getting populated the figures could appear in your totals (if entered in a cell your referencing).