Solved: 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 :(

[rory]

Can you post a sample workbook, please (use Manage attachments in the Go Advanced section) - it'll be easier to test and to simplify some of that code.

[marshybid]

Example workbook attached as requested.

Marshybid :help

[rory]

Try this:

Dim pc As PivotCache, pt As PivotTable, pi As PivotItem
Dim varSubsOff, varItemList, varItem
varSubsOff = Array(False, False, False, False, False, False, False, False, False, False, False, False)
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'Raw Data'!R1C1:R8000C47", Version:=xlPivotTableVersion10)

Set pt = pc.CreatePivotTable(TableDestination:="'Overview Summary'!R3C1", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10)

With pt
With .PivotFields("LOS")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Job Number")
.Orientation = xlRowField
.Position = 2
.Subtotals = varSubsOff
End With
With .PivotFields("Job Title")
.Orientation = xlRowField
.Position = 3
.Subtotals = varSubsOff
End With
With .PivotFields("Manage Internal Recruitment Process")
.Orientation = xlRowField
.Position = 4
.Subtotals = varSubsOff
End With
.AddDataField .PivotFields("Application Status"), "Count of Application Status", xlCount
With .PivotFields("Application Status")
.Orientation = xlColumnField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False
On Error Resume Next

.PivotFields("LOS").PivotItems("(blank)").Visible = False

With .PivotFields("Application Status")
.PivotItems("CV Submitted").Visible = True
For Each pi In .PivotItems
If pi.Name <> "CV Submitted" Then pi.Visible = False
Next pi
varItemList = Array("CV Submitted", "Rejected by Hiring Manager at CV Review", "1st Interview", _
"2nd Interview", "3rd Interview", "4th Interview", "Rejected by Hiring Manager Following Interview", _
"Offer of Intent", "Offered", "Offer Accepted", "Offer Declined", "Hired")
For Each varItem In varItemList
Set pi = .PivotItems(varItem)
pi.Visible = True
pi.Position = .VisibleItems.Count
Set pi = Nothing
Next varItem
On Error GoTo 0
End With
End With

[marshybid]

Rory, you are a star :o) One issue though, I have a piece of code further into my macro which hides a column of data (it has to left present in the Pivot Items initially to ensure that all data comes through, then the column is hidden) The code is:

Range("A4").Select
Do Until ActiveCell.Value = "Grand Total"
If ActiveCell.Value = "Pending Applicants" Then
ActiveCell.EntireColumn.Hidden = True
End If

ActiveCell.Offset(0, 1).Select

Loop

When I added your code above and ran the macro, the pivot table is created fine, with the Pivot Items in the right place, but I get an error message saying Run-time error '1004' - Application-defined or object-defined error. When I click on Debug it highlights the line:

ActiveCell.Offset(0, 1).Select

Any thoughts??

Thanks for all your help.

Marchybid

[rory]

I would guess it's not finding "Grand Total" and you end up trying to select a cell off the side of the worksheet! Care to post the entire code? (Note: it is almost never necessary to select a range to work with it, and it actually slows down your code.)

[marshybid]

Rory, realised that I wasn't setting the sheet to Sheets("Detail").Select (Doh!!!)

Works just fine now.

I'm very new to VBA and am generally doing things through trial and error, most of it works, just gets a bit tricky to refine at times.

Thanks a lot for your help.

Marshybid

[rory]

Glad to help. One of the joys(?) of learning any programming language is looking back at what you did years or even months ago, and marvelling at how much better you could do it now! (hopefully) :)