Pivot Table custom sort order

[andielangley]

I have some VBA which successfully creates a pivot table but three of the fields use custom lists for a sort order. How do I get my pivot table to sort the agerange, servrange and salrange fields according to my custom lists as defined in Tools > Options > Custom Lists? Any help would be appreciated. My pivot table code is shown below.

Cells.Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        ActiveCell.CurrentRegion.Address).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1"
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Sex", _
        "Contract", "agerange"), ColumnFields:=Array("servrange", "salrange"), _
        PageFields:="Department"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Payroll No").Orientation = _
        xlDataField

 

[xlbo]

As long as the list is stored in your custom lists, if the field entries match that list, just doing an autosort (ascending or descending) should put it in the same order as your list:
syntax as follows:

ActiveSheet.PivotTables("PivotTable4&quot.PivotFields("ROD&quot.AutoSort xlAscending , "ROD"

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244