Can't get VBA to create a Calculated Field in Pivot Table

[TheRedCardinal]

I have created a sequence in VBA that makes a Pivot Table out of a named data Range - Data.

The data sections work fine, but I need a new field that calculates the Sum of the two data fields.

I tried this:

With ActiveSheet.PivotTables("SalesPivotTable").CalculatedFields.Add "Difference", _
        "='VAT Value' + Amount", True

But when I try to proceed, it gives me an error, highlighting the "Difference" bit and saying Expected: End of Statement.

Have I set the syntax up incorrectly?

 

[theBardd]

This works for me

    With ActiveSheet.PivotTables("SalesPivotTable")
    
        .CalculatedFields.Add "Difference", "='VAT Value' +Amount", True
    End With

 

[TheRedCardinal]

Nope still not working for me.
I don't get an error now in the VBA module, but running the macro creates the pivot table just fine, but doesn't add the Calculated field.

Here is an extract from my code:

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("VAT Value")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Amount (VAT) "
End With


'Insert Calculated Field


With ActiveSheet.PivotTables("SalesPivotTable")
    .CalculatedFields.Add "Difference", "='VAT Value' + Amount", True
    .Orientation = xlDataField
    .Position = 3
End With

I've added the section for one of the data fields just to show the names.

What am I missing?

 

[theBardd]

Slight tweak to include field name works for me

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("VAT Value")
    .Orientation = xlDataField
    .Position = 2
    .Function = xlSum
    .NumberFormat = "#,##0"
    .Name = "Amount (VAT) "
End With

'Insert Calculated Field

With ActiveSheet.PivotTables("SalesPivotTable")
    .CalculatedFields.Add "Difference", "='VAT Value' + Amount", True
    .PivotFields("Difference").Orientation = xlDataField
End With