I have a single source file ("Source" tab) where I am formatting calculations using arrays of SUMPRODUCT formula ("Calculations" tab), as I need to pull data based on multiple criteria (Product, Country, Measure etc). Also, as new data is added monthly, direct cell references aren't possible.
As further calculations are processed on this data, I have split this data out into seperate Country based worksheets and am linking the country and measure cell from a manually-entered cell, so I can reuse the single calculation worksheet and copy the data for each country and measure into a more accessible and seperate worksheet, as no further processing is required, so I just copy the data using Paste Special > Values ("Country" tab).
While this process is successful, I have come up against a huge processing lag, where the SUMPRODUCT calculation process on the "Calculations" tab effectively cripples my machine. I have since selected "manual calculation" as once the source data is calculated it and copied out, I have no further need to refresh the data - however, I have VB automated the process to copy the data into the respective country worksheets, yet it doesn't take it account that the SUMPRODUCT formula is still busy calculating/refreshing cells when it runs - so it can quite easily populate all of the country worksheets, when the data is technically still processing the first country.
So now you have the background data, my questions are as follows:
1) As the SUMPRODUCT calculations are a one time process, is it possible to process it as a VB query and retain cell ranges some of which are static, while others are dynamic - for example, a single cell of the calculation currently looks like:
=SUMPRODUCT(-('Source paste'!$A$1:$A$1000='QA Calculations'!$B9),-('Source paste'!$B$1:$B$1000='QA Calculations'!$C9),-('Source paste'!$C$1:$C$1000='QA Calculations'!$D9),-('Source paste'!$D$1:$D$1000='QA Calculations'!$E9),-('Source paste'!$E$1:$E$1000='QA Calculations'!$F9),-('Source paste'!$F$1:$F$1000='QA Calculations'!$G9),'Source paste'!G$1:G$1000)
Presumably, converting this into VB form would appear like:
ActiveCell.Formula = "=SUMPRODUCT(-('Source paste'!$A$1:$A$1000='QA Calculations'!$B9),-('Source paste'!$B$1:$B$1000='QA Calculations'!$C9),-('Source paste'!$C$1:$C$1000='QA Calculations'!$D9),-('Source paste'!$D$1:$D$1000='QA Calculations'!$E9),-('Source paste'!$E$1:$E$1000='QA Calculations'!$F9),-('Source paste'!$F$1:$F$1000='QA Calculations'!$G9),'Source paste'!G$1:G$1000)"
As I only need the rows as a dynamic selection, is there any way I could automate this process across the entire worksheet? I feel some form of looping could solve this?
This way seems the best way, as it's a once-only process and I can configure the process with the notification the data is processed, before I run the VB process to copy the countries out to their respective worksheets.
As further calculations are processed on this data, I have split this data out into seperate Country based worksheets and am linking the country and measure cell from a manually-entered cell, so I can reuse the single calculation worksheet and copy the data for each country and measure into a more accessible and seperate worksheet, as no further processing is required, so I just copy the data using Paste Special > Values ("Country" tab).
While this process is successful, I have come up against a huge processing lag, where the SUMPRODUCT calculation process on the "Calculations" tab effectively cripples my machine. I have since selected "manual calculation" as once the source data is calculated it and copied out, I have no further need to refresh the data - however, I have VB automated the process to copy the data into the respective country worksheets, yet it doesn't take it account that the SUMPRODUCT formula is still busy calculating/refreshing cells when it runs - so it can quite easily populate all of the country worksheets, when the data is technically still processing the first country.
So now you have the background data, my questions are as follows:
1) As the SUMPRODUCT calculations are a one time process, is it possible to process it as a VB query and retain cell ranges some of which are static, while others are dynamic - for example, a single cell of the calculation currently looks like:
=SUMPRODUCT(-('Source paste'!$A$1:$A$1000='QA Calculations'!$B9),-('Source paste'!$B$1:$B$1000='QA Calculations'!$C9),-('Source paste'!$C$1:$C$1000='QA Calculations'!$D9),-('Source paste'!$D$1:$D$1000='QA Calculations'!$E9),-('Source paste'!$E$1:$E$1000='QA Calculations'!$F9),-('Source paste'!$F$1:$F$1000='QA Calculations'!$G9),'Source paste'!G$1:G$1000)
Presumably, converting this into VB form would appear like:
ActiveCell.Formula = "=SUMPRODUCT(-('Source paste'!$A$1:$A$1000='QA Calculations'!$B9),-('Source paste'!$B$1:$B$1000='QA Calculations'!$C9),-('Source paste'!$C$1:$C$1000='QA Calculations'!$D9),-('Source paste'!$D$1:$D$1000='QA Calculations'!$E9),-('Source paste'!$E$1:$E$1000='QA Calculations'!$F9),-('Source paste'!$F$1:$F$1000='QA Calculations'!$G9),'Source paste'!G$1:G$1000)"
As I only need the rows as a dynamic selection, is there any way I could automate this process across the entire worksheet? I feel some form of looping could solve this?
This way seems the best way, as it's a once-only process and I can configure the process with the notification the data is processed, before I run the VB process to copy the countries out to their respective worksheets.
