Piovt Table Sorting
Results 1 to 4 of 4

Thread: Piovt Table Sorting
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2012
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Piovt Table Sorting

    I use the macro below to sort a Pivot table descending based on the value cell I have selected in Pivot Table value section, regardless of how many Row Labels there are. It works for a standard Pivot Table, but not one that has been added to the Data Model. Any ideas as to what needs to be changed?


    Code:
    Sub SortAllRowFields_ZASelVal()
    'select the Value field that
    ' the sort will be based on
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim df As PivotField
    Dim strVal As String
    
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    If pt Is Nothing Then Exit Sub
    Set df = ActiveCell.PivotField
    MsgBox df
    If df.Orientation <> xlDataField Then
      MsgBox "Please select a Values field"
      Exit Sub
    Else
      strVal = df.Caption
    End If
    For Each pf In pt.RowFields
      pf.AutoSort xlDescending, strVal
    Next pf
    MsgBox "Row fields were sorted Z-A " _
      & vbCrLf _
      & "based on the Value field: " _
      & vbCrLf _
      & strVal
    End Sub

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,658
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Piovt Table Sorting

    try
    Code:
    Dim pf as PivotFields
    (?)
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  3. #3
    Board Regular
    Join Date
    Aug 2012
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Piovt Table Sorting

    Added the "s" and ran macro...Nothing..

  4. #4
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,658
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Piovt Table Sorting

    hmm. My suggestion was from macro-recording applying a Sort to a Data-Model-Pivot Table.
    That was the first thing I saw and expected it to cascade. I won't get a good chance to see again until tomorrow (Saturday)
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

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
  •