Sorting Pivot Items in a pivot table??

[marshybid]

Hi there,

I am struggling to sort pivot items in the correct order in a pivot table. Code below:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'Raw Data'!R1C1:R8000C47", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="'Overview Summary'!R3C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Overview Summary").Select
Cells(2, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("LOS")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Job Number")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Job Number").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Job Title")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Job Title").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Manage Internal Recruitment Process")
.Orientation = xlRowField
.Position = 4
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Manage Internal Recruitment Process").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Application Status"), "Count of Application Status" _
, xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status")
.Orientation = xlColumnField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False

On Error Resume Next
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status")
.PivotItems("Auto-closed").Visible = False
.PivotItems("Cancelled").Visible = False
.PivotItems("Closed").Visible = False
.PivotItems("In process").Visible = False
.PivotItems("New").Visible = False
.PivotItems("Open").Visible = False
.PivotItems("Telephone Screen").Visible = False
.PivotItems("Withdrawn").Visible = False
.PivotItems("Rejected").Visible = False
End With
On Error GoTo 0
On Error Resume Next
ActiveSheet.PivotTables("PivotTable2").PivotFields("LOS").PivotItems _
("(blank)").Visible = False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("CV Submitted").Position = 1
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Rejected by Hiring Manager at CV Review").Position = 2
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("1st Interview").Position = 3
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("2nd Interview").Position = 4
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("3rd Interview").Position = 5
ActiveSheet.PivotTables("PivotTable1").PivotFields("Application Status").PivotItems _
("4th Interview").Position = 6
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Rejected by Hiring Manager Following Interview").Position = 7
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Offer of Intent").Position = 8
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Offered").Position = 9
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Offer Accepted").Position = 10
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Offer Declined").Position = 11
ActiveSheet.PivotTables("PivotTable2").PivotFields("Application Status").PivotItems _
("Hired").Position = 12
On Error GoTo 0

When the pivot table is completed the pivot items do not always appear in the order as instructed above!! I have added On Error Resum Next etc, but the order seems to have a mind of its own. If all of the pivot items are present it works fine, but when some are missing (quite often the case) it doesn't work.

Can anyone help with this please.

Thanks,

Marshybid

 

[SkipVought]

Hi,

This code has nothing at all to do with a SORT in a PivotTable.

Is there a reason that you are coding the creation of the PivotTable and the PivotFields you are displaying? Why not just create the PT and refresh the source data or point to new source data?

As far as the POSITIONING of the PivotItems within any PivotField, I'd do this...

1. Make a list of ALL possible PivotItem Values. (This is often a risky issue, when new, unexpected PivotItems values appear. I would query each NEW source data set to ADD new itms to this list)
2. use the .PivotCache.MissingItemsLimit = xlMissingItemsNone to Clear the PivotCache of non-existent items
3. Loop thru the PivotItems in the PivotField, using the LIST mentioned above to order the available PivotItem Values.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}