Sort pivot table on grand total column

[CookieMonster76]

Hi

I need to sort a pivot table on the "Grand total" column. I have tried using the recorder, but i can't get it to work.

The code I have is below, would anyone know how to finish it off please?

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim rgPivot As Range
Dim SrcData As String
Dim pf As String
Dim pf_Name As String
SrcData = Sheets("Salary Source").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
Set sht = Sheets("Salary")
Set rgPivot = sht.Range("A1")
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)
Set pvt = pvtCache.CreatePivotTable(TableDestination:=rgPivot, TableName:="Salary Costs")
With pvt
.PivotFields("Surname").Orientation = xlRowField
.PivotFields("Element").Orientation = xlColumnField
.ManualUpdate = False
.AddDataField .PivotFields("Value"), pf_Name, xlSum
pf_Name = "Value"
.DataBodyRange.NumberFormat = "#,##0.00;(#,##0.00)"
.InGridDropZones = True
.RowAxisLayout xlTabularRow


End With

Thanks

Paul

 

[jondavis1987]

It looks like you need to put End in the line after end with.