VBA Custom sort pivot field

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,674
Office Version
365
Platform
Windows
Hi,

(Adjusted), I have the following code which custom sorts the given pivot field:
Rich (BB code):
Public Sub Pivot_Data()

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .AddCustomList wMain.Range("List_Status").Value
    End With

    With wPivot1.PivotTables(1)
        .ChangePivotCache ThisWorkbook.PivotCaches.Create(xlDatabase, "Data!" & Data_Range(True).Address(, , xlR1C1))
        .RefreshTable

        .SortUsingCustomLists = False
        .PivotFields("Status").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, ordercustom:=Application.CustomListCount + 1
    End With

    With Application
        .DeleteCustomList .CustomListCount
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub
Part in blue works.

I now need to custom sort two pivot fields; I tried adjusting code to following, but second sort (red) doesn't change order, all values are in alphabetical order only.

I have checked data and it does contain values for each column I want to custom sort (test purposes hardcoded this into the data set to force the output).

As the data for the pivot table continuously changes, I need the code to refresh/update the pivot for each data import. I tried to manually re-sort the column but each time I refresh the pivotcache the column defaults to alphabetical order

Pivotfield "Status" is a row field
Pivotfield "Group_&_MP" is a column field
Rich (BB code):
Public Sub Pivot_Data()

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .AddCustomList wMain.Range("List_Status").Value
    End With

    With wPivot1.PivotTables(1)
        .ChangePivotCache ThisWorkbook.PivotCaches.Create(xlDatabase, "Data!" & Data_Range(True).Address(, , xlR1C1))
        .RefreshTable

        .SortUsingCustomLists = False
        .PivotFields("Status").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, ordercustom:=Application.CustomListCount + 1

        Application.DeleteCustomList Application.CustomListCount
        Application.AddCustomList wMain.Range("List_Group_MP").Value

        .SortUsingCustomLists = False
        .PivotFields("Group_&_MP").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, ordercustom:=Application.CustomListCount + 1
    End With
  
    With Application
        .DeleteCustomList .CustomListCount
        .ScreenUpdating = True
        .EnableEvents = True
    End With    
    
End Sub
Any suggestions?

TIA,
Jack
 
Last edited:

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,674
Office Version
365
Platform
Windows
Still curious to hear suggestions to make above work, however, as a solution this works and will replace part in red:
Code:
Sub Macro1()
    
    Dim x   As Long
    Dim v   As Variant: v = Range("List_Group_MP").Value

    With wPivot2.PivotTables(1)
        For x = LBound(v, 1) To UBound(v, 1)
            .PivotFields("Group_&_MP").PivotItems(CStr(v(x, 1))).Position = x
        Next x
        .RefreshTable
    End With
    
    Erase v
    
End Sub
Inspired by @Jerry Sullivan and recording code for manual field change (http://tinyurl.com/y2chwucg)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,889
Messages
5,489,551
Members
407,700
Latest member
SimpleJuan

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top