I get an error while adding value fields to pivot

Question

G'day,

I have an excel macro where i combine my data at the end into a pivot table. However i also have a sub that adds a value field to the table. It looks like this:

Sub AddValuesField()
    Dim pvt As PivotTable
    Dim pf As String
    Dim pf_Name As String
    
    Dim sDatasheet  As String
    Dim sPivotName  As String
    sPivotName = "Pivot1"
    sDatasheet = "Documents with extracted fields"

    Sheets(sPivotName).Select
    Set pvt = Sheets(sPivotName).PivotTables("PivotTable1")
   
    pf = Sheets(sDatasheet).Range("A1").Value
    pvt.AddDataField pvt.PivotFields(pf), pf, xlSum
End sub

The name of the column is stored in pf(pivotfield). I however get the most descriptive error i dream of receiving: "1004 object defined error"

I hope that someone here can help me out :)

gr, Menno

Answer 1

When adding a field as Sum to your Pivot-Table, you need to add the words "Sum of" before the PivotField name.

Change your line:

pvt.AddDataField pvt.PivotFields(pf), pf, xlSum

to:

pvt.AddDataField pvt.PivotFields(pf), "Sum of " & pf, xlSum

Note: you don't need to Select your worksheet, you can simply use Set pvt = Sheets(sPivotName).PivotTables("PivotTable1") without selecting it first.