Can I Sort (A-Z or Z-A) Pivot Table Data or Fields using VBA?

[MikeZz]

Hi,
I have some VBA that makes changes to multiple Pivot fields at once to show
various data in a Pivot Chart.

Is there a clean way to have VBA sort the Pivot by say the Grand Total
Column (either reverse or ascending) so that the pivot bar chart looks like a
pareto.... tallest bars at the left of the stacked bar chart?

As an example of a simple single column ("Customer") & row ("Group") field,
I want to sort the data so that the customer with highest sales is at the
top of the list.

Here's a recording of a macro but it just seems to reference cells in the
worksheet. The problem is that if I change the colum/row fields, the
cell/column/row of the total will also change.

Range("I7").Select
Selection.Sort Key1:="R7C9", Order1:=xlAscending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
Selection.Sort Key1:="R7C9", Order1:=xlDescending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
Range("A7").Select
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom
Range("B6").Select
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlLeftToRight
Selection.Sort Order1:=xlDescending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlLeftToRight

 

[ryguy7272]

Seems like you are familiar with macro recording, but just to be sure...
1) Turn on the macro recorder
2) Double-click the column you want to sort by
3) Click advanced
4) AutoSort Options – Ascending/Descending
5) Click Ok, Click Ok
6) Turn off the macro recorder
7) Place the codes in the appropriate order of your sub…remember VBA is very
procedural, with the exception of IF...Then, calling Subs, etc.

Alternatively, with the macro recorder on, copy the Pivot Table, and
paste-special on a new sheet, then do the operations you want to do. That
may be an alternative to the option I mentioned above.

Good luck!
Ryan--

 

[MikeZz]

Hi Ryan,
I've already tried recording a macro, the problem is that it doesn't seem to
make any direct reference to what in the pivot table to sort by.... the
recorder just picks a cell range.

I want to tell it to do a Reverse Sort "Customer Field" no matter what cell
it's actually in (because it may change from a columnfield to a row field
depending on what chart I want to make.... does this make sense?

Thanks again,
Mike

 

[ryguy7272]

Recorded Macro:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R40C36", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Sheet3!R3C1",
TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion10

Recorded Macro - Modified:
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("Sheet1").Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:=Sheets("Sheet2").Range("A3"),
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

Pay attention to the SourceData part! Try recording the macro again, and
then change the SourceData part of the code like this:
..Range("A1").CurrentRegion).

Be mindful of the periods. Also, I am at school now, and all we have here
is Office 2007, so the recorded macro (first one listed is 2007 code). The
second recorded macro (the second one listed) is 2003 code, I use Office 2003
for my work-work. Experiment and see what happens…


Regards,
Ryan---

 

[MikeZz]

Hey Ryan,
Thanks for the persistance. When I originally tried to record the macro, I
just clicked on a cell and hit the sort button. I totally missed the
instructions to go into the pivot field advanced settings. Now that I
followed instructions, the code looks very easy to incorporate into my
current VBA.

Thanks again!