VBA Calculated Field - Subtract Value in Data Field from Value in Row Field

[th259]

Below is a screenshot of my pivottable. I want to change Total column to state Total Expenses. The Grant Award is a Row Field and not a Data Field, how do I add the code to subtract, Grant Award minus Total Expenses.

Below was the code for the VBA setup.

'=============================================================================
' Data_Pivot Macro
'=============================================================================
' Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim pt As PivotTable
Dim PRange As Range
Dim lastRow As Long
Dim LastCol As Long

' Declare Variables
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pay10_Pivot").Delete ' Delete Data Pivot
Sheets.Add Before:=ActiveSheet ' Add New Sheet before Active Sheet
ActiveSheet.Name = "Pay10_Pivot" ' Name New Sheet as Data Pivot
Application.DisplayAlerts = True
Set PSheet = Worksheets("Pay10_Pivot")
Set DSheet = Worksheets("Pay10_Workings")

' Define Data Range
lastRow = DSheet.Cells(DSheet.Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, DSheet.Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(lastRow, LastCol)

' Define Pivot Cache
    Set PCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=PRange)
    Set pt = PCache.CreatePivotTable( _
        TableDestination:=PSheet.Cells(1, 1), TableName:="Pay10_Data")

' Insert Row Fields
With pt
    .PivotFields("Loc").Orientation = xlRowField
    .PivotFields("Loc Name").Orientation = xlRowField
    .PivotFields("Loc Name").EntireColumn.AutoFit
    .PivotFields("Function").Orientation = xlRowField
    .PivotFields("Resource").Orientation = xlRowField
    .PivotFields("Grant Award").Orientation = xlRowField
    .PivotFields("Grant Award").NumberFormat = "#,##0.00_);[Red](#,##0.00)"  ' Format Number with red for negatives
End With

' Disable subtotals on row Field 1 through 5
With pt
    .PivotFields("Loc").Subtotals(1) = False
    .PivotFields("Loc Name").Subtotals(1) = False
    .PivotFields("Function").Subtotals(1) = False
    .PivotFields("Resource").Subtotals(1) = False
    .PivotFields("Grant Award").Subtotals(1) = False
End With
' Insert Data Field
With pt.PivotFields("Amount")
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "#,##0.00_);[Red](#,##0.00)" ' Format Number
    .Name = "Sum of Amount"
End With
' Pivot Table Tabular View
With pt
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    .MergeLabels = True
End With
With pt.PivotFields("Loc Name")
    .AutoFit
    .DataRange.Cells(1).Resize(, .DataRange.Columns.Count).ColumnWidth = 41.57
    .DataRange.Cells.HorizontalAlignment = xlLeft
End With
With pt.PivotFields("Grant Award").DataRange
    .Cells.HorizontalAlignment = xlRight
End With