Ok guys - a challenge - summing from unknown worksheets

[Minthe]

I have a workbook that I've created with 4 main worksheets. The first is a "logon" worksheet - the user selects a "path" to follow by clicking on one of the command buttons. (The purpose of the workbook is to dynamically create voucher sheets for expense reimbursement by project. Each project has its own workbook - each workbook has multiple worksheets.)

So far so good. BUT (user requirement) the user renames each worksheet. Of course, there is no main list of possible names - the user could name a worksheet 'INeedAStiffDrink' if desired. The user can NOT alter the layout of the worksheet however so all the sums for each sheet are always in G39, G40, and G41.

THEN I have a worksheet called Summary. And, you guessed it, this sheet needs to summarize all the data found in cells G39, G40, and G41 for all worksheets found within the workbook.

How do I do this since I don't know beforehand the names of the worksheets nor the number of worksheets in the workbook???

And yes, I know this would be easier in Access but that's not an option.

Thanks in advance. After reading through the forum listing I figure you guys will definitely have a way to do this!

Minthe

[BabyTiger]

Hi,

You can try to count to number of sheets on your workbook first:

i = Application.Sheets.Count

The above code will identify the number of worksheets on your workbook. Then based on that, you can go through each sheet to find the data you wanted:

For i = Application.Sheets.Count To 1 Step -1
If Application.Sheets(i).Name <> "Logon" And Application.Sheets(i).Name Then
Value = Application.Sheets(i).range("G40").value
End If
Next i

HTH

[Yogi Anand]

Hi Minthe:
Look at DATA|CONSOLIDATE feature -- it is intended to provide the consolidation summary in your SUMMARY sheet (data is pulled from the appropriate worksheets in the Workbook).
So, if I as a user change the name of one or more worksheets, you don't have to be concerned by whether the user changed the name of the worksheet or not.

HTH
Please post back if it works for you ... otherwise explain a little further and let us take it from there!

[Minthe]

This where I am so far - I found another post with code that places the names of each of the worksheets in column A. I thought then I could try to use that as a reference to tell Excel the name of the worksheet to reference. (getting in way over my head).

The Data/consolidate feature might be an answer - but I can't figure out how to tell it to consolidate from all worksheets in the book, not just the current worksheet? Esp since I won't know the names of the worksheets in the workbook ahead of time?

(I am I'm explaining this well enough!)

[ This Message was edited by: Minthe on 2002-03-25 21:13 ]

[Dave Patton]

You could create 2 dummy worksheets and name them Start1_02 and End1_02; these names hopefully are unusual enough that no one will want them.

Set these 2 sheets before and after possible active sheets.

Hide the 2 Sheets.

Formula =sum(Start1_02:End1_02!G39)

[Yogi Anand]

On 2002-03-25 21:08, Minthe wrote:
This where I am so far - I found another post with code that places the names of each of the worksheets in column A. I thought then I could try to use that as a reference to tell Excel the name of the worksheet to reference. (getting in way over my head).

The Data/consolidate feature might be an answer - but I can't figure out how to tell it to consolidate from all worksheets in the book, not just the current worksheet? Esp since I won't know the names of the worksheets in the workbook ahead of time?

(I am I'm explaining this well enough!)

[ This Message was edited by: Minthe on 2002-03-25 21:13 ]

Hi Minthe:
As I understand it, you have a workbook with a number of worksheets that have been templated with input on certain cells that facilitate totalling on some cell like G40. You will setup your consolidation on the summary sheet before hand referring to the worksheets by their generic names viz sheet1, sheet2, ...
What somebody does after that to the names of the worksheets is immaterial -- you ought to look at HELP on consolidation, or some examples of it to get a good feel for it.

[Dave Hawley]

Hi Minthe

If you are using VBA then you should always use a sheets CodeName (it's the one nOT in brackets in the Project Explorer) this cannot be changed by a user.

If you simly have a number of Worksheet functions in your "Summary" sheet you only need to ensure it is always the last sheet. The use:

=SUM(Sheet1:Summary!G4) This way any sheets added between these 2 sheets will automatically be include.

Here is a VBA Function

Function SumAllSheets(rSumRange As Range)
Dim sSheet As Worksheet
Dim MyVal
Application.Volatile
For Each sSheet In ActiveWorkbook.Worksheets
If sSheet.CodeName <> "Sheet6" Then
MyVal = WorksheetFunction.Sum _
(sSheet.Range(rSumRange.Address), MyVal)
End If
Next
SumAllSheets = MyVal
End Function

[Minthe]

THANK YOU EVERYONE!

I truly appreciate everyone's help and input. I was at my wit's end. The suggestion that seemed to be easiest and work the best for me was from the two Dave's.

You guys single-handedly(double-handedly?)ended HOURS of frustration for me. As you can probably tell, I'm definitely NOT an Excel expert! You guys are great!