Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA
Related Links:
1. Create an Excel Pivot Table report using VBA
2. Referencing an Excel Pivot Table Range using VBA
3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA
4. Excel Pivot Table Address, Location, Move & Copy using VBA
5. Excel Pivot Table Layout and Design, using VBA
6. Excel Pivot Table Properties & Settings, using VBA
7. Refresh Excel Pivot Table and Cache of PivotTable, using VBA
8. Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA
9. Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA
10. Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA
11. Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA
12. Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA
13. Create & Customize Excel Pivot Table Charts, using VBA
Refer complete Tutorial on using Pivot Tables in Excel Window (user interface):
Create and Customize a Pivot Table report
————————————————————————————————————–
Contents:
1. Sort a PivotTable report: set sort order of a field using the PivotField.AutoSort method
2. Use the Range.Sort Method to sort a range of values in a PivotTable report
3. Sort a PivotTable report – set the sort order manually
4. Sort a PivotTable report using Custom Lists
————————————————————————————————————–
1. Sort a PivotTable report: set sort order of a field using the PivotField.AutoSort method
This method has 4 arguments – Order, Field, PivotLine and CustomSubtotal, of which Order and Field arguments are mandatory to specify. Field is the name of the sort key field. PivotLine is a line on a column or row in the report. CustomSubtotal is the Custom Sub total field.
Example 1: Sort by Value – sort the City field in ascending order, based on the Sum of Sales – refer Image 1.
Sub PivotTableSort1()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“City”).AutoSort Order:=xlAscending, Field:=“Sum of Sales”
End Sub
Example 2: Sort Text Field – sort the City field in ascending order – refer Image 2.
Sub PivotTableSort2()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“City”).AutoSort Order:=xlAscending, Field:=“City”
End Sub
2. Use the Range.Sort Method to sort a range of values in a PivotTable report
In this method, the Key1 argument specifies the first sort field – it is a Range Object or a Range Name. The Order1 argument specifies the sort order for the values in Key1. Type argument specifies the elements to be sorted. The Second and Third sort fields – arguments Key2 and Key3 – cannot be used to sort a PivotTable. OrderCustom argument specfies the custom order in the list of custom sort orders, by adding 1 to the the number of the custom order in the list viz. for a custom order appearing at number 5 in the list, the OrderCustom will be 6.
Example 3: Sort City in descending order – refer Image 3.
Sub PivotTableSort3()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“City”).DataRange.Sort Order1:=xlDescending, Type:=xlSortLabels
End Sub
Example 4: Sort City by Sum of Sales in ascending order – refer Image 4.
Sub PivotTableSort4()
Dim PvtTbl As PivotTable
Dim rngKey1 As Range
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
Set rngKey1 = PvtTbl.PivotFields(“Sum of Sales”).DataRange.Cells(1)
PvtTbl.PivotFields(“Sum of Sales”).DataRange.Sort key1:=rngKey1, Order1:=xlAscending, Type:=xlSortValues, Orientation:=xlTopToBottom
End Sub
Example 5: Sort Region by Sales-Index (values field) in descending order using values in the row of SubCompact – refer Image 5.
Sub PivotTableSort5()
Dim PvtTbl As PivotTable
Dim rngKey1 As Range
Set PvtTbl = Worksheets(“Sheet8”).PivotTables(“PivotTable1”)
Set rngKey1 = PvtTbl.PivotFields(“Car Models”).PivotItems(“SubCompact”).DataRange.Cells(1)
PvtTbl.PivotFields(“Car Models”).PivotItems(“SubCompact”).DataRange.Sort key1:=rngKey1, Order1:=xlDescending, Type:=xlSortValues, Orientation:=xlLeftToRight
End Sub
Example 6: Sort Car Models by Sales-Index (values field) in descending order using values in the column of North America – refer Image 6.
Sub PivotTableSort6()
Dim PvtTbl As PivotTable
Dim rngKey1 As Range
Set PvtTbl = Worksheets(“Sheet8”).PivotTables(“PivotTable1”)
Set rngKey1 = PvtTbl.PivotFields(“Car Models”).PivotItems(“SubCompact”).DataRange.Cells(1)
PvtTbl.PivotFields(“Car Models”).PivotItems(“SubCompact”).DataRange.Sort key1:=rngKey1, Order1:=xlDescending, Type:=xlSortValues, Orientation:=xlTopToBottom
End Sub
3. Sort a PivotTable report – set the sort order manually
Example 7: Set position numbers for all items – refer Image 7.
Sub PivotTableSortManually1()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“City”).PivotItems(“Toronto”).Position = 1
PvtTbl.PivotFields(“City”).PivotItems(“Nice”).Position = 2
PvtTbl.PivotFields(“City”).PivotItems(“Paris”).Position = 3
PvtTbl.PivotFields(“City”).PivotItems(“Vancouver”).Position = 4
PvtTbl.PivotFields(“City”).PivotItems(“London”).Position = 5
PvtTbl.PivotFields(“City”).PivotItems(“New York”).Position = 6
PvtTbl.PivotFields(“City”).PivotItems(“Leeds”).Position = 7
PvtTbl.PivotFields(“City”).PivotItems(“Los Angeles”).Position = 8
End Sub
Example 8: Loop through all items in a PivotTable Field and specify position number – in this example the postions in Example 7 have been reversed – refer Image 8.
Sub PivotTableSortManually2()
Dim PvtTbl As PivotTable
Dim pvtItm As PivotItem
Dim iPosNo As Integer
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
For Each pvtItm In PvtTbl.PivotFields(“City”).PivotItems
iPosNo = InputBox(“Enter Position Number for “ & pvtItm)
pvtItm.Position = iPosNo
Next
End Sub
4. Sort a PivotTable report using Custom Lists
If a custom list is available, sorting is done in a PivotTable using this, else default sorting is used. If a custom list data exactly matches the source data, PivotTable sorting uses the custom list order to sort. This may make the sort order to appear random ie. PivotField may give an appearance of being unsorted. Excel has 4 built-in custom lists – 2 for the seven weekdays (Sunday … Saturday; Sun … Sat) and 2 for the twelve calendar months (January … December; Jan … Dec).
Custom Lists are specifically useful in sorting, in cases where you want sorting to be done in a customized order. PivotTable gives options of sorting in Ascending and Descending order which sorts items alphabetically (A to Z or Z to A). But in some cases like when you want a Pivot Report monthwise or as per weekdays, you will want a sorting order of January to December or Monday to Sunday. Sorting based on Ascending or Descending will not return this order. Obviously you can sort manually, but this could be painful and not preferable. If you have a customized list containing the 12 months in the order of January to December, then using the Ascending sort option will return the sort order for the months as January to December (and not alphabetically). If you want a sort order of December to January, use the Descending sort option or else insert a custom list in the order of December to January and use the Ascending sort order. Excel has default custom lists available for the 12 calendar months and the 7 weekdays, which are generally required by users, and you can further add your own custom lists. Obviously you can sort the months alphabetically too, starting with April, August, …. by disabling the custom lists option as explained below.
(i) Add a custom list using the Application.AddCustomList Method
(a) using an array:
Application.AddCustomList Array(“Plant One”, “Plant Two”, “Plant Three”, “Plant Four”, “Plant Five”)
(b) using a worksheet range:
Dim rng As Range
Set rng = Worksheets(“Sheet13”).Range(“H2:H6”)
Application.AddCustomList rng
(ii) Delete a custom list using the Application.DeleteCustomList Method
(a) Delete the last custom list:
Application.DeleteCustomList Application.CustomListCount
(b) Delete a custom list by custom list number:
‘delete the last custom list:
Dim iCustomListNum As Integer
iCustomListNum = Application.CustomListCount
Application.DeleteCustomList iCustomListNum
or:
‘delete specific custom list:
Application.DeleteCustomList 5
(iii) Determine the contents of a custom list with the Application.GetCustomListContents Method
‘determine the contents of the custom list number 5 and copy to worksheet cells:
Dim n As Integer
clArray = Application.GetCustomListContents(5)
For n = LBound(clArray, 1) To UBound(clArray, 1)
Worksheets(“Sheet13”).Cells(20, n) = clArray(n)
Next n
(iv) Determine the custom list number from the custom list array, using the Application.GetCustomListNum Method
Dim n As Integer
n = Application.GetCustomListNum(Array(“Plant One”, “Plant Two”, “Plant Three”, “Plant Four”, “Plant Five”))
MsgBox n
(v) Sort PivotTable report using Custom Lists – the SortUsingCustomLists Property
Syntax: PivotTableObjectVariable.SortUsingCustomLists
PivotTable.SortUsingCustomLists Property – this property controls use of custom lists while sorting. Setting this to False will not allow sorting based on custom lists except when a custom list is selected or specified (say by OrderCustom argument) in the sort method.
Example 9: In this case SortUsingCustomLists property has been set to false, and even though a custom list is present, AutoSort does not use the custom list – refer Image 9.
Sub PivotTableSortUsingCustomLists1()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet14”).PivotTables(“PivotTable1”)
Application.AddCustomList Array(“Plant One”, “Plant Two”, “Plant Three”, “Plant Four”, “Plant Five”)
PvtTbl.SortUsingCustomLists = False
PvtTbl.PivotFields(“Plant”).DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels
End Sub
Example 10: Refer Image 10. In this case SortUsingCustomLists property has been set to False, yet sorting is done using the custom list because OrderCustom is mentioned in the Sort method which specifies the custom list to be used. Note that there are 4 built-in custom lists in excel, so the first custom list which is created will be number 5. Note further that for a custom list appearing at number 5, the OrderCustom will be 6.
Sub PivotTableSortUsingCustomLists2()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet14”).PivotTables(“PivotTable1”)
Application.AddCustomList Array(“Plant One”, “Plant Two”, “Plant Three”, “Plant Four”, “Plant Five”)
PvtTbl.SortUsingCustomLists = False
PvtTbl.PivotFields(“Plant”).DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=6
End Sub
Example 11: In this case SortUsingCustomLists property has been set to True, and AutoSort uses the custom list – refer Image 11.
Sub PivotTableSortUsingCustomLists3()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet14”).PivotTables(“PivotTable1”)
Application.AddCustomList Array(“Plant One”, “Plant Two”, “Plant Three”, “Plant Four”, “Plant Five”)
PvtTbl.SortUsingCustomLists = True
PvtTbl.PivotFields(“Plant”).DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels
End Sub
Example 12: In this case SortUsingCustomLists property has been set to True, and there are 2 custom lists, here AutoSort uses the custom list which appears last – refer Image 12.
Sub PivotTableSortUsingCustomLists4()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet14”).PivotTables(“PivotTable1”)
Application.AddCustomList Array(“Plant One”, “Plant Two”, “Plant Three”, “Plant Four”, “Plant Five”)
Application.AddCustomList Array(“Plant One”, “Plant Two”, “Plant Three”, “Plant Five”, “Plant Four”)
PvtTbl.SortUsingCustomLists = True
PvtTbl.PivotFields(“Plant”).AutoSort Order:=xlAscending, Field:=“Plant”
End Sub
Example 13: In this case SortUsingCustomLists property has been set to True, and there are 2 custom lists, here sorting is done using the OrderCustom number (Note that there are 4 built-in custom lists in excel, so the first custom list which is created will be number 5. For a custom list appearing at number 5, the OrderCustom will be 6.) – refer Image 13.
Sub PivotTableSortUsingCustomLists5()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet14”).PivotTables(“PivotTable1”)
Application.AddCustomList Array(“Plant One”, “Plant Two”, “Plant Three”, “Plant Four”, “Plant Five”)
Application.AddCustomList Array(“Plant One”, “Plant Two”, “Plant Three”, “Plant Five”, “Plant Four”)
PvtTbl.SortUsingCustomLists = True
PvtTbl.PivotFields(“Plant”).DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=6
End Sub