Pivot Table Error 1004 when adding Fields

Question

I have this macro that creates a pivot table from a ranged data ("PvtData"). I keep getting run time error 1004 (Application defined or Object defined error) when the code is trying to add PivotFields.

Below is a portion of the code;

Workbooks(PivotFile).PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "PvtData", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="PvtResult", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion14

'Work up to here

With ActiveSheet.PivotTables("PivotTable1").PivotFields("COB")
    .Orientation = xlColumnField
    .Position = 1
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
    .Orientation = xlRowField
    .Position = 1
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Book")
    .Orientation = xlRowField
    .Position = 2
End With

I've tried recording the macro, which gives the same code line.

Changing the table destination to empty ("") does not help.

I tried your code. It didn't give any error. Can you Set pvtTable = Excel.Sheets("UrSheet").PivotTables("PivotTable1") pvtTable.AddFields ("COB") — Siva
@Siva Thanks! That works like a charm! but how can i set it as row field, column field, or data field?? — vbanoob27
Here is the Syntax for it. .AddFields(RowFields, ColumnFields, PageFields, AddToTable). — Siva
@Siva Thank you for the reply, but i don't understand how to elaborate them with e.g "COB" or "Product". Sorry i am relatively new to VBA :) — vbanoob27
@Siva found them out here. Thanks a lot for your help!! msdn.microsoft.com/en-us/library/office/ff837987.aspx — vbanoob27

Siva Siva · Accepted Answer · 2016-02-02T06:52:08

After adding the fields try your piece of code as it is.

pvtTable.AddFields("COB")
With pvtTable.PivotFields("COB")
.Orientation = xlColumnField
.Position = 1
End With

Pivot Table Error 1004 when adding Fields

1 Answers