excelvbapivot# I get an error while adding value fields to pivot

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

Solution

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.

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel