I'm trying to use a macro to sort numeric values in a pivot table based on a column selected in another sheet.
I've managed to capture the column number of the pivot on which the data should be sorted. This is stored in a variable, pivotColToSort.
How can I pass the value of this column number into the code below, substituting it with the name of the pivot field? For example, if pivotColtoSort = 3, and the third column in the pivot table is "Count of Sales", I need the code to return:
[pre].PivotTables("myPivotTable"
.PivotFields("Region".AutoSort _<br />
mySortOrder, "Count of Sales", .PivotTables("myPivotTable". _<br />
PivotColumnAxis.PivotLines(1), 1[/pre]
I guess I could make a static key, and reference that:
Col PivotField
----------------
1 Region
2 SalesPerson
3 Count of Sales
4 Max of Sales
5 etc.
But for I was hoping to dynamically determine the name of the pivot field based on the column value.
Suggestions? Thank you.
I've managed to capture the column number of the pivot on which the data should be sorted. This is stored in a variable, pivotColToSort.
How can I pass the value of this column number into the code below, substituting it with the name of the pivot field? For example, if pivotColtoSort = 3, and the third column in the pivot table is "Count of Sales", I need the code to return:
[pre].PivotTables("myPivotTable"
.PivotFields("Region".AutoSort _<br />mySortOrder, "Count of Sales", .PivotTables("myPivotTable"
. _<br />PivotColumnAxis.PivotLines(1), 1[/pre]
I guess I could make a static key, and reference that:
Col PivotField
----------------
1 Region
2 SalesPerson
3 Count of Sales
4 Max of Sales
5 etc.
But for I was hoping to dynamically determine the name of the pivot field based on the column value.
Suggestions? Thank you.