Summing Generated Worksheets

[Guest]

I've got a stack of code to generate reports for sales advisers. One
workbook containing one worksheet per sales adviser.

I've also got code to bulk up these reports into teams, so one workbook per
team, with one worksheet per adviser (worksheet name = adviser name).

What I want to do is put in a summary (or manager) sheet on the team workbook.

As all adviser sheets are structurally the same, it's just a case of making
formulas which look at the same point on each sheet, so something like
=SUM(First Sheet:Last Sheet!H:H)

Thing is, I don't know how many sheets there will be, or what they'll be
called.

I've wondered about doing it as follows:

Define a variable to hold the first sheet name, likewise the last sheet
name, then bung in the manager sheet and set the formulas to the variable
values.

Thing is, how do I get the first and last sheet names into variables? (Esp.
the last - thet'll be the difficult one)

Any help you can give would be great.

Cheers,

Tom.

 

[Ron de Bruin]

Hi


Tip: from Dave Peterson

I'd create two new worksheets--one to the far right and one to the far left. Call them Start and End
Then using a sheet (Summary) that is outside this "sandwich" of worksheets with this formula:
=Sum(Start:End!A1)

Then you can drag sheets in and out of that sandwich to play what if games.
I'd put a couple of notes on each of these sheets: "don't delete this sheet!"
And protect the worksheets so that people don't use it for real data.


See also
http://www.rondebruin.nl/linksum.htm

 

[Guest]

Thanks, Ron.

I spotted the advice, but it only offsets the problem.

I can programatically add a 'start' sheet, but how would I add an 'end' one
without knowing how many sheets were present or their names?

This all needs to be within VBA because this will be happening for hundreds
of advisers each month, so probably about 60 teams.

Cheers,

Tom.

 

[Ron de Bruin]

Why not make a summary sheet (formulas)
http://www.rondebruin.nl/summary.htm

Or (copy/paste)
http://www.rondebruin.nl/copy2.htm


When you want to have the data

 

[Guest]

Thanks.

Both of those look like realistic, workable solutions.

Cheers Ron.

mr tom.

 

[Guest]

Ron - you're a legend!

I'm going to go for the Merge cells from some or all worksheets into one
Master sheet. I'm going to use the UsedRange and use all sheets.

Just one question:
If I held this code in a control file, how would I go about making it
cycle through eg. all workbooks in a particular folder and put in the Master
sheet to each of them?

Thanks again. You've really made my week.

Tom.

 

[Ron de Bruin]

Hi mr tom

See my site for examples to loop through workbooks in a folder

 

[Guest]

Thanks, Ron.

I really should have noticed that one first time through.

Tom.