Can someone offer help with code that will programmaticaly loop thru a
worksheet collection in a workbook, determine the largest sum of two
cells within a monthly range, and summarize those results in a summary
worksheet? The following is provided in the hope it clarifies what I
am trying to achieve.

Situation:
A workbook contains 116 data worksheets each of which are identical
except for the values of their cells. Worksheet names are determined
by value of cell A1 in each respective worksheet (format "000"). A
simplified example of the first worksheeet named "001" is as follows:

A B C D E ... G
1 001
2 1/1/04 Olgivy $900 $800 more...data
3 1/8/04 Larson $300 $100 more...data
4 2/2/04 Guillett $700 $600 more...data
5 2/7/04 Peterson $800 $400 more...data
6 3/6/04 Kabel $500 $300 more...data
7 3/9/04 de Bruin $400 $200 more...data

I place a summary worksheet named "Consolidation" after worksheet 116
and use it to derive various sums of values from the collection of 116
worksheets.

Question:
How would one programatically loop through all 116 data worksheets
beginning with the first worksheet, determine the largest sum of range
Cx:Dx (where x=row number) that occured during each month (number of
rows per month varies from month to month) and list those results for
each worksheet within range N1:Z116 of the Consolidation worksheet to
achieve the following? Column N lists name of worksheet, and Columns
O:Z are used for monthly largest sum of Cx:Dx.

N O P Q R S ... Z
1 1/04 2/04 3/04 4/04 5/04 12/04
2 001 $1700 $1300 $800
3 002 (loop thru all 116 worksheets and repeat for each sheet)
4 003

I have been unable to locate any prior posts that illustrate methods
to determine the largest sum of two cells in a range and where the
range limits vary according to dates. Harder to explain than I thought
it would be...hope someone can weigh in with possibilites.

TIA for any response...

Mike Taylor

Can you explain what to you mean by "Largest sum of range Cx:Dx' ?
Do you mean add C2+C3+C4..... + D2+D3+D4.... for each sheet,
and out of 116 sheets, the which is largest, to be identified ?

Sharad

"Mike Taylor" wrote in message
om...
Can someone offer help with code that will programmaticaly loop thru a
worksheet collection in a workbook, determine the largest sum of two
cells within a monthly range, and summarize those results in a summary
worksheet? The following is provided in the hope it clarifies what I
am trying to achieve.

Situation:
A workbook contains 116 data worksheets each of which are identical
except for the values of their cells. Worksheet names are determined
by value of cell A1 in each respective worksheet (format "000"). A
simplified example of the first worksheeet named "001" is as follows:

A B C D E ... G
1 001
2 1/1/04 Olgivy $900 $800 more...data
3 1/8/04 Larson $300 $100 more...data
4 2/2/04 Guillett $700 $600 more...data
5 2/7/04 Peterson $800 $400 more...data
6 3/6/04 Kabel $500 $300 more...data
7 3/9/04 de Bruin $400 $200 more...data

I place a summary worksheet named "Consolidation" after worksheet 116
and use it to derive various sums of values from the collection of 116
worksheets.

Question:
How would one programatically loop through all 116 data worksheets
beginning with the first worksheet, determine the largest sum of range
Cx:Dx (where x=row number) that occured during each month (number of
rows per month varies from month to month) and list those results for
each worksheet within range N1:Z116 of the Consolidation worksheet to
achieve the following? Column N lists name of worksheet, and Columns
O:Z are used for monthly largest sum of Cx:Dx.

N O P Q R S ... Z
1 1/04 2/04 3/04 4/04 5/04 12/04
2 001 $1700 $1300 $800
3 002 (loop thru all 116 worksheets and repeat for each sheet)
4 003

I have been unable to locate any prior posts that illustrate methods
to determine the largest sum of two cells in a range and where the
range limits vary according to dates. Harder to explain than I thought
it would be...hope someone can weigh in with possibilites.

TIA for any response...

Mike Taylor

application.Evaluate("Max(if(Month(A2:A30)=2,C1:C3 0+D1:D30,0))")

Should give you the largest sum for a particular month.

You would just need to write code to generate the correct formula.
(basically, build the formula string, then pass it to evaluate).

--
Regards,
Tom Ogilvy



"Mike Taylor" wrote in message
om...
Can someone offer help with code that will programmaticaly loop thru a
worksheet collection in a workbook, determine the largest sum of two
cells within a monthly range, and summarize those results in a summary
worksheet? The following is provided in the hope it clarifies what I
am trying to achieve.

Situation:
A workbook contains 116 data worksheets each of which are identical
except for the values of their cells. Worksheet names are determined
by value of cell A1 in each respective worksheet (format "000"). A
simplified example of the first worksheeet named "001" is as follows:

A B C D E ... G
1 001
2 1/1/04 Olgivy $900 $800 more...data
3 1/8/04 Larson $300 $100 more...data
4 2/2/04 Guillett $700 $600 more...data
5 2/7/04 Peterson $800 $400 more...data
6 3/6/04 Kabel $500 $300 more...data
7 3/9/04 de Bruin $400 $200 more...data

I place a summary worksheet named "Consolidation" after worksheet 116
and use it to derive various sums of values from the collection of 116
worksheets.

Question:
How would one programatically loop through all 116 data worksheets
beginning with the first worksheet, determine the largest sum of range
Cx:Dx (where x=row number) that occured during each month (number of
rows per month varies from month to month) and list those results for
each worksheet within range N1:Z116 of the Consolidation worksheet to
achieve the following? Column N lists name of worksheet, and Columns
O:Z are used for monthly largest sum of Cx:Dx.

N O P Q R S ... Z
1 1/04 2/04 3/04 4/04 5/04 12/04
2 001 $1700 $1300 $800
3 002 (loop thru all 116 worksheets and repeat for each sheet)
4 003

I have been unable to locate any prior posts that illustrate methods
to determine the largest sum of two cells in a range and where the
range limits vary according to dates. Harder to explain than I thought
it would be...hope someone can weigh in with possibilites.

TIA for any response...

Mike Taylor