VBA Custom sort pivot field

[JackDanIce]

Hi,

(Adjusted), I have the following code which custom sorts the given pivot field:

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

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

 

[JackDanIce]

Still curious to hear suggestions to make above work, however, as a solution this works and will replace part in red:

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)