Custom sorting pivot tables

[efrank02]

Hey there,

I'm working on code which loads data into multiple pivot tables in a user form. I want to sort some tables to be sorted alphabetically, and some to be sorted custom (specifically, AAA, AA, A, BBB, BB, B).

I'm new to VB and not sure if there's a sorting property for the PivotTable object that I can play around with to only sort some tables like I mentioned or if I should approach it a different way.

Any help would be greatly appreciated, thanks.

 

[Andrew Poulsom]

Welcome to MrExcel.

What version of Excel are you using?

 

[efrank02]

I'm currently using 2003, but I have 2007 as well.

 

[Andrew Poulsom]

Look here:

http://office.microsoft.com/en-us/excel-help/sort-a-pivottable-or-pivotchart-report-HP005202971.aspx

The macro recorder will give you some code that you can adapt.

 

[efrank02]

Thanks a lot, I've recorded a macro that so that the custom sort for pivot tables works in the normal workbook.

The problem I'm having is the pivot table I'm trying to sort is in a user form, and I can't figure out how to apply the Custom Sort List that I've made.

In the VB code, there's a function that loads the pivot table into the form- is there a field on the PivotTable object that allows me to sort it?

Here's the macro I made if it helps:
Range("J6").Select '(this is the cell with the field name)
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=6, Orientation:=xlTopToBottom

Maybe there's a way to .Select the PivotTable in the form?

 

[Andrew Poulsom]

If your pivot table is from the Office Web Components this shows you how to apply a custom sort:

http://office.microsoft.com/en-us/a...e-pivotchart-views-in-access-HA001034579.aspx