Manipulating Pivot Table via VBA

[studious]

I have daily data that looks like this:

[TABLE="width: 515"]
<colgroup><col span="2"><col span="3"><col span="3"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Code[/TD]
[TD]Filter 1[/TD]
[TD]Filter 2[/TD]
[TD]Filter 3[/TD]
[TD]Var1[/TD]
[TD]Var2[/TD]
[TD]Var3[/TD]
[/TR]
[TR]
[TD="align: right"]5-Jan-14[/TD]
[TD="align: right"]3050[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD="align: right"]15.9[/TD]
[TD="align: right"]15.9[/TD]
[/TR]
[TR]
[TD="align: right"]5-Jan-14[/TD]
[TD="align: right"]3050[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD="align: right"]15.9[/TD]
[TD="align: right"]15.9[/TD]
[/TR]
[TR]
[TD="align: right"]5-Jan-14[/TD]
[TD="align: right"]5470[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD="align: right"]24.75[/TD]
[TD="align: right"]21.1[/TD]
[TD="align: right"]21.1[/TD]
[/TR]
[TR]
[TD="align: right"]6-Jan-14[/TD]
[TD="align: right"]3050[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD="align: right"]15.9[/TD]
[TD="align: right"]15.9[/TD]
[/TR]
[TR]
[TD="align: right"]6-Jan-14[/TD]
[TD="align: right"]3050[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD="align: right"]15.9[/TD]
[TD="align: right"]15.9[/TD]
[/TR]
[TR]
[TD="align: right"]6-Jan-14[/TD]
[TD="align: right"]5470[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD="align: right"]24.75[/TD]
[TD="align: right"]21.1[/TD]
[TD="align: right"]21.1[/TD]
[/TR]
[TR]
[TD="align: right"]7-Jan-14[/TD]
[TD="align: right"]3050[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD="align: right"]15.9[/TD]
[TD="align: right"]15.9[/TD]
[/TR]
[TR]
[TD="align: right"]7-Jan-14[/TD]
[TD="align: right"]3050[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD="align: right"]24.75[/TD]
[TD="align: right"]21.1[/TD]
[TD="align: right"]21.1[/TD]
[/TR]
[TR]
[TD="align: right"]7-Jan-14[/TD]
[TD="align: right"]5470[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD="align: right"]28.55[/TD]
[TD="align: right"]24.5[/TD]
[TD="align: right"]24.5[/TD]
[/TR]
</tbody>[/TABLE]

I have used a pivot table to roll it up to summarize it by day (average) and then grouped it to get to a weekly level. Resulting in this:

[TABLE="width: 446"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Average of Var1[/TD]
[TD]Average of Var2[/TD]
[TD]Average of Var3[/TD]
[/TR]
[TR]
[TD]1/5/2014 - 1/8/2014[/TD]
[TD="align: right"]25.7[/TD]
[TD="align: right"]18.58888889[/TD]
[TD="align: right"]18.58888889[/TD]
[/TR]
</tbody>[/TABLE]

I am using Filters 1, 2, 3 and Code as filters to in the pivot table to calculate:

• Average of Var1 for the Week, but only where Code is 3050
• Percentage difference in Var1 for Code 3050 and Code 5470, calculated as (WeekAvgVar1-3050 - WeekAvgVar1-5470) / WeekAvgVar1-3050&5470
• The same as above, but with Filter 1 applied for the nominator and Filter 2 applied for the denominator

Since I have to do more of these calculations with different filters on, my question is whether there is a clever way to code/retrieve the results (VBA, I assume) instead of me doing the calculations manually for each case? My concern is that switching filters on and off manually and copy/pasting results is very error-prone and I would really like to avoid it.

Thank you!

 

[SpillerBD]

Before PivotTables were the SumProduct formulas. Now, depending on version, we have SUMIFS, AVERAGEIFS, AGGREGATE and more.
There is also a strange technique of building a Pivot Table from a Pivot Table.
...and while you may not have access to the add-in for PowerPivot, there is PowerBI desktop with its DAX calculations that can give a once is done.

A VBA solution should be in your reach since it will mostly be a formula and fill-down to have the desired calculations. Since you will establish the "last row" to properly due the fill down you will also have the ranges for the overall averages.

On the Filter question, there are Pivot Table filters that can be applied or Auto-Filters applied to a Pivot Table range...?

 

[studious]

Thanks, SpillerBD.

I don't understand the difference in filters you mention. I have the 3 filter variables and Code in the Filters field of the Pivot Table.

You say the calculations with different filters on and off can be done in VBA. I am still novice in VBA, so if you or anyone here could provide a sample for one of the variables to point me in the right direction, I can pick it up from there.

Thank you.