Sort Pivot Table based on selected Cell

[NateD1]

Hi All, i currently have the below code which works perfectly on one of my pivots (pivot has several data columns - a Value for each column in field settings) which sorts based on cell selection.
However this code doesn't work for my other pivot which has multiple columns of data but comes from only one value in the field settings. is there any reason why this wont sort? thanks

Sub Pivot_Sort()
Application.DisplayAlerts = False
'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

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]

my other pivot which has multiple columns of data but comes from only one value in the field settings

Perhaps I just haven't had enough caffeine yet, but I don't understand what you mean by this. Can you elaborate, perhaps with a screenshot?

 

[NateD1]

Perhaps I just haven't had enough caffeine yet, but I don't understand what you mean by this. Can you elaborate, perhaps with a screenshot?

sorry for the confusion, i have just recorded the below macro that id actually like it to sort my pivot table by any column that I click on, and not just fixed to column (6) as shown in the code below. is it possible to amend the below to do this? thanks

Sub SortPivot()

    ActiveSheet.PivotTables("PivotTable1").PivotFields("NAME").AutoSort _
        xlDescending, "Sum of Time", ActiveSheet.PivotTables("PivotTable1"). _
        PivotColumnAxis.PivotLines(6), 1
End Sub

 

[RoryA]

I think you want something like this:

Sub Pivot_Sort_by_Item()
   Dim pt As PivotTable
   Dim pi As PivotItem
   
   On Error Resume Next
   Set pt = ActiveCell.PivotTable
   If pt Is Nothing Then Exit Sub
   Set pi = ActiveCell.PivotItem
   On Error GoTo 0
   If Not pi Is Nothing Then
      pt.PivotFields("NAME").AutoSort _
              xlDescending, "Sum of Value", pt.PivotColumnAxis.PivotLines(pi.Position), 1
   End If
End Sub

 

[NateD1]

thank you! the code works but for some reason when i select the column to sort.. it sorts the column to right of the one clicked and when i click the last column it errors the macro as there is no column to the right. below is a screenshot of my data fields. any idea why this would be happening?

 

[RoryA]

Do you have any values in the Reason code column that are not visible in the pivot table?

 

[NateD1]

its working now! thank you!

 

[RoryA]

Then I'm confused. It works fine here. Perhaps try this, which simply uses the relative position of the active cell in its parent field's label range:

Sub Pivot_Sort_by_Item()
   Dim pt As PivotTable
   Dim pi As PivotItem
   
   On Error Resume Next
   Set pt = ActiveCell.PivotTable
   If pt Is Nothing Then Exit Sub
   Set pi = ActiveCell.PivotItem
   On Error GoTo 0
   If Not pi Is Nothing Then
      Dim idx As Long
      idx = ActiveCell.Column - pi.Parent.LabelRange.Column + 1
      pt.PivotFields("NAME").AutoSort _
              xlDescending, "Sum of Value", pt.PivotColumnAxis.PivotLines(idx), 1
   End If
End Sub

 

[NateD1]

apologies, was my error- had to change the word value to Time, thanks for you help on this one