Pivot Autosort

[NateD1]

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?

 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

 

[RoryA]

Add this:

On Error Goto 0

after this line:

If pt Is Nothing Then Exit Sub

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

 

[NateD1]

i added the below however nothing happened, should i add it in a particular place within the code?

On Error GoTo 0
If pt Is Nothing Then Exit Sub

 

[RoryA]

If you didn't see any messages then the code is running without error, which suggests it is doing what it should.

 

[NateD1]

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]

I can't see why that would be. Any chance you can upload the file somewhere for me to have a look at?

 

[NateD1]

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 Name	Duration	Agent Name
Code Reason 1	43.00	Agent Name 1
Code Reason 1	38.00	Agent Name 2
Code Reason 3	47.00	Agent Name 3
Code Reason 1	24.00	Agent Name 4
Code Reason 2	24.00	Agent Name 5
Code Reason 2	16.00	Agent Name 1
Code Reason 2	30.00	Agent Name 2
Code Reason 1	5.00	Agent Name 3
Code Reason 1	24.00	Agent Name 4
Code Reason 1	9.00	Agent Name 5
Code Reason 3	3.00	Agent Name 1
Code Reason 1	13.00	Agent Name 2
Code Reason 3	19.00	Agent Name 3
Code Reason 2	48.00	Agent Name 4
Code Reason 2	16.00	Agent Name 5
Code Reason 2	17.00	Agent Name 1
Code Reason 1	20.00	Agent Name 2

 

[RoryA]

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?