Piovt Table Sorting

brawnystaff

Board Regular
Joined
Aug 9, 2012
Messages
81
Office Version
365
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,682
Messages
5,488,241
Members
407,632
Latest member
varunwalla

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