Pivot Autosort

NateD1

New Member
Joined
Apr 1, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Hi All, below is my current code i use to auto sort my pivot based on cell selected in the pivot. this previously worked on a pivot table but on a new workbook its randomly stopped working.
does anyone have an alternative code or can this code be simplified to work?

VBA Code:
 Sub Pivotsort()
Application.DisplayAlerts = False

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

If df.Orientation <> xlDataField Then
  MsgBox "Please select a Values field"
  Exit Sub
Else
  If pt.PivotCache.OLAP = True Then
    strVal = df.Name
  Else
    strVal = df.Caption
  End If
End If

For Each pf In pt.RowFields
  pf.AutoSort xlDescending, strVal
Next pf
Application.DisplayAlerts = True


End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Add this:

Code:
On Error Goto 0

after this line:

Code:
If pt Is Nothing Then Exit Sub

then run the code again and tell us what the error is.
 

NateD1

New Member
Joined
Apr 1, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
i added the below however nothing happened, should i add it in a particular place within the code?
VBA Code:
On Error GoTo 0
If pt Is Nothing Then Exit Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you didn't see any messages then the code is running without error, which suggests it is doing what it should.
 

NateD1

New Member
Joined
Apr 1, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

sorry for the confusion, its not that I'm getting error messages. My code usually works and sorts my columns in my pivot based on a cell selected in the pivot and then run macro button. I've created a new pivot using the same Macro however this time it wont work, it will only work if I right click and sort a column by Ascending first then click my macro to sort Descending. i have checked my data source and all seems fine with no gaps.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I can't see why that would be. Any chance you can upload the file somewhere for me to have a look at?
 

NateD1

New Member
Joined
Apr 1, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Sorry i couldnt upload to here however ive copied some dummy data of my data source hopefully it will let you copy and paste it. quite basic data set im using, i have pivoted. using Columns: code name, Rows:Agent name, Values:Duration.

Code NameDurationAgent Name
Code Reason 143.00Agent Name 1
Code Reason 138.00Agent Name 2
Code Reason 347.00Agent Name 3
Code Reason 124.00Agent Name 4
Code Reason 224.00Agent Name 5
Code Reason 216.00Agent Name 1
Code Reason 230.00Agent Name 2
Code Reason 15.00Agent Name 3
Code Reason 124.00Agent Name 4
Code Reason 19.00Agent Name 5
Code Reason 33.00Agent Name 1
Code Reason 113.00Agent Name 2
Code Reason 319.00Agent Name 3
Code Reason 248.00Agent Name 4
Code Reason 216.00Agent Name 5
Code Reason 217.00Agent Name 1
Code Reason 120.00Agent Name 2
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It works for me. That's why I wanted to see your actual workbook. Do you not have a Onedrive/Dropbox type service you can upload to?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,492
Members
417,028
Latest member
JFCLUK

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
Top