VBA Custom sort pivot field
Results 1 to 2 of 2

Thread: VBA Custom sort pivot field
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,238
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    3 Thread(s)

    Default VBA Custom sort pivot field

    Hi,

    (Adjusted), I have the following code which custom sorts the given pivot field:
    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
    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 by JackDanIce; Jul 18th, 2019 at 03:51 AM.


  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,238
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Custom sort pivot field

    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 by JackDanIce; Jul 18th, 2019 at 05:01 AM.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •