modifying formulas with code

  • Thread starter Thread starter Rick B
  • Start date Start date
R

Rick B

I have a workbook where values in row C contain worksheet names. VBA code
creates additional worksheets naming the worksheet with what is entered in
column C. There is also a summary worksheet which Sums like cells from all
the worksheets. Looking for assistance in creating code which would modify
the SUM formula on the summary worksheet each time new worksheets are added.

-Rick
 
While you can do that, I'd suggest a different approach.

Create two blank sheets, named "First" and "Last" (or some other
convenient name). Put the "First" sheet to the left of your data sheets
and put the "Last" sheet to the right of your data sheets. You can then
hide them if you wish. Then in the summary sheet, use (for example, to
sum all A1 cells):

=SUM(First:Last!A1)

In our code that creates additional sheets, use the Before argument of
the Worksheets.Add method to make sure additional sheets come before
"Last", e.g.:

Worksheets.Add(Before:=Sheets("Last"))

That way, the formulas will be self-updating.
 
yep... that's what I did last year when I needed such a
thing..

it's simple, and it works great.
 
a better solution would be to have your sheet like (represents the tab
order)

<summary> <start> <-- other sheets --> <end>

put all you sheets between two sheets, one named start and one named end.

then you summary formulas would be

=Sum(Start:End!A1:B9)

this is called a 3D reference and only works with a few formula functions,
one of which is the SUM function.

any sheets between Start and End in the tab order will be included in the
sum.
 
That's the same thing as the other solution, with the
exception that you called the sheets 'Start' and 'End', as
opposed to 'First' and 'Last',

and you suggested the 3d range.

if the 3d range is better as far as the summing, please
explain why.
 
oh, perhaps you meant 'better' relative to the original
idea, not 'better' as compared to J.E. McGimpsey's
suggestion...

sorry.
 
It is the same as JE's suggestion which was not visible when I posted it or
I wouldn't have wasted my time.

It is better than the OP's request for a macro to adjust the sum formulas
for each added worksheet which would be kludgy to say the least.
 
Being an experienced user of this method, it is suprising you didn't
recognize it - reading disability perhaps or maybe my bad typing.
 
Being an experienced user of this method, it is suprising
you didn'trecognize it - reading disability perhaps or
maybe my bad typing.


I did recognize the method. I was about to suggest it,
but I saw JE's post, and just concurred with it, since he
had suggested it already.

So, basically, you, me, and JE, all agreed.

I just accidentally misinterpreted your first post to be
saying that your solution was better than JE's, when you
meant to be saying that your solution was better than the
original poster's idea. I agree with you.

Not so much a 'reading disability'... just a slight
misunderstanding, which I recognized a minute later, and
reposted with an apology for my hastiness in asking why
your solution was better than JE's (which was not what you
had meant to be saying).

Good night.

On to other challenges (like driving home in the current
East Coast ice... good thing I love snow and ice).
 
Any ideas for a variation on this -

I have sheets set like this-

<MonthType1><MonthType2><MonthType3><PreviousMonthType1><PreviousMonthType2><PreviousMonthType3
etc....

I want to maintain the order of the grouping, but summarize each typ
(SummaryType1, SummaryType2, SummaryType3) with formulae that wil
automatically update when I add new sheets each month. Is i
possible?

Seems like the First:Last trick won't quite work given the groupin
order of the sheets...

Thanks for any help
 
I considered that, but the problem is - group1 contains worksheets o
type1, type2, and type3. Group2 contains the three types as well.
want the summary pages to summarize by type, not by group. But, stil
maintain the grouping structure of the worksheets. I think I must b
missing something?

I am pretty much resigned to the fact I'll just have to copy ol
worksheets and update the formulas manually. No big deal, I was jus
hoping for something slicker.
 
Back
Top