Hey guys, need a little help and can't seem to find the answer i need.
I have VBA code that creates a pivot table and my first pass hardcoded the Values chosen into the building of the table. I would like to try and simplify my code and use a variable to define what field is put into the Values column of the pivot table;
So first i have done this;
The range is merged cells with drop down applied, users can choose "Release1, Release2, Release3 etc" All of the options in this drop down exist in the base data for pivot.
Then in the code itself i am trying to get the pivot table to look at the variable and pull back the correct field into the values, this is where i find my issues;
any ideas? Thanks in advance as always you bunch of helpful people!
I have VBA code that creates a pivot table and my first pass hardcoded the Values chosen into the building of the table. I would like to try and simplify my code and use a variable to define what field is put into the Values column of the pivot table;
So first i have done this;
VBA Code:
Dim release As Range
Set release = Range("B9:D9")
The range is merged cells with drop down applied, users can choose "Release1, Release2, Release3 etc" All of the options in this drop down exist in the base data for pivot.
Then in the code itself i am trying to get the pivot table to look at the variable and pull back the correct field into the values, this is where i find my issues;
VBA Code:
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields(release), "Sum of" & release, xlSum
any ideas? Thanks in advance as always you bunch of helpful people!