Pivot Table Sort by Data Column

[VBA learner ITG]

I have used the below VBA code from other post to generate a test Pivot Table and now I have been asked to sort the Data which is the Values Column by Highest Number in descending Order.

I have tried to amend the code but i keep getting error messages with different VBA codes i have tried.

can you help solve my conundrum at all?




Dim ws As Worksheet, pc As PivotCache, pt As PivotTable
Set ws = Worksheets.Add
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Data!A1:M2000")
Set pt = pc.CreatePivotTable(ws.Range("A3"))
'Setting Fields
With pt
With .PivotFields("Project Manager")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Project Cost")
.Orientation = xlColumnField
.Position = 1
End With
.AddDataField .PivotFields("Project Cost"), "Sum of Print Cost", xlSum
End With

 

[Jerry Sullivan]

Here's some code you can try...

Sub MakePivotThenSortValues()
 Dim ws As Worksheet, pc As PivotCache, pt As PivotTable
 
 Set ws = Worksheets.Add
 Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Data!A1:M2000")
 Set pt = pc.CreatePivotTable(ws.Range("A3"))
 
 'Setting Fields
 With pt
   With .PivotFields("Project Manager")
      .Orientation = xlRowField
      .Position = 1
   End With
   
   .AddDataField .PivotFields("Project Cost"), "Sum of Print Cost", xlSum
 
   .PivotFields("Project Manager").AutoSort _
        xlDescending, "Sum of Print Cost"

 End With

End Sub

I've eliminated the part of your code in which you added Project Cost as a Column Field. I'm assuming that you were just trying to show the values in a single column instead of having a separate column for each unique Project Cost value.