Excel 2003 VBA Macro to Calulate values from multiple worksheets

[wbow]

Have an Excel 2003 workbook containing multiple sheets;
“ProjectedVolumes”
“ProductLines”
“Supplier1”
“Supplier2”
“Supplier3”
and so on
“SummaryByLine”

“ProjectedVolumes”
A B C D E
ProductLine Year1 Year2 Year3 Year4
Code1 X X X X
Code2 X X X X
Code3 X X X X

“Supplier1”
A B C D E
ProductLine QtyPer UnitCost BxC Value
Code1 X X X
Code2 X X X
Code3 X X X

A B C D E
ProductLine Year1 Year2 Year3 Year4
Supplier1
Supplier2
Supplier3


What I would like to do is set up a command button to “Calculate” by Supplier the
“Value” of each code and to copy that value into the “SummaryByLine” sheet
after the user has entered details in the areas marked with an X.

I know that I need to loop through the ProductLine columns for each supplier in
the “Supplier” sheets, then get the “ProjectedVolume”, then perform the calculation.
Whilst I understand the logic of what I’m attempting my VBA skills are so poor that I’m not making any real headway.

 

[SkipVought]

hi,

You have shot yourself in the foot with your workbook design. Of course, it might not be YOUR design.

With a proper design, you could probably accomplish your requirements EITHER with a few mouse clicks in less than 2 minutes with a PivotTable, OR by using an aggregation spreadsheet function on your summary sheet. NO VBA REQUIRED!

Here's what you ought to do...

1) NORMALIZE the data in both ProjectedVolumes & ProductLines, using faq68-5287

2) CONSOLIDATE the data in your supplier sheets into ONE table

That would mean you would end up with THREE source data tables; a much simple source structure to work with.

If you want to try this approch, let me know, ESPECAILLY that "your VBA skills are poor."



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wbow]

Skip,
Quite right, the workbook isn't mine. Tried to get the owner to "normalise" but it feeds into a corporate scheme that gets normalised after the detailed breakout I've shown. They need the workbook as is.

Looks like the manual formula route I initially set up as a temporary solution is going to be the final fix.

No worries, thanks for looking anyway.

 

[SkipVought]

There is nothing keeping your from creating those 3 sheets and using them to drive your summary, leaving the original sheets as they are. That creation could be automated, via VBA.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wbow]

Skip,
Sorry, though it was a dead duck without normalising.
If you could get me started using the sheets as they are, that would be an enormous help. The lady using the workbook is spending hours calculating values across hundreds of rows, and her
fear is missing a modification to a sumif or vlookup which has happened in the past.

Again, many thanks.

 

[SkipVought]

The lady using the workbook is spending hours calculating values across hundreds of rows, and her
fear is missing a modification to a sumif or vlookup which has happened in the past.

THAT is a HUGE part of the issue you face with your current workbook structure.

With a NORMALIZED workbook, your formulas will be greatly simplified. Here's an example...
[tt]
Your SUPPLIER Sheet

A B C D E
SupplierID ProductLine QtyPer UnitCost Ext Value
ABC Code1 X X X
ABC Code2 X X X
ABC Code3 X X X
XYZ Code1 X X X
XYZ Code2 X X X
XYZ Code3 X X X
...
[/tt]
Using Named Range...

Total value for each supplier...
[tt]
SupplierID TOTAL
ABC =SUMIF(SupplierID,A1,Ext_Value)
XYZ COPIED FORMULA
[/tt]
...not knowing what your summary calculates.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]