Piovt Table Sorting

brawnystaff

Board Regular
Joined
Aug 9, 2012
Messages
104
Office Version
  1. 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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
try
Code:
Dim pf as PivotField[COLOR=#0000ff]s[/COLOR]
(?)
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top