Excel - Runtime error ‘1004’ AddFields method of Pivot Table failed 1

[jupops]

Good Afternoon

Could you please help? I have tried using a macro (see code below) I recorded to compile a Pivot Table, but the second time I ran I got the error meaaage “Runtime error ‘1004’ AddFields method of Pivot Table failed,, and when I ran debug it highlights ‘ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Fork Area"’ – which seems to stop the process when it is adding the Fork Area range (from Column N) into the Row field in the Pivot Table.

The macro was for a simple Pivot Table which was being created on the same sheet as the data in cell Q4 and was using the range Fork Area (N Column) and getting a sum total of SCR QTY (column D) – this was placed in the Data felid

Thank you in advance for your help

Regards

Jupops


Sub Macro1()
'
' Macro1 Macro

Columns("A:N").Select
Range("N1").Activate
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!C1:C14").CreatePivotTable TableDestination:="[test1.xls]Sheet1!R4C17" _
, TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Fork Area"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SCR QTY")
.Orientation = xlDataField
.Caption = "Sum of SCR QTY"
.Function = xlSum
End With
ActiveWindow.SmallScroll ToRight:=3
Columns("Q:R").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[SkipVought]

Hi,

Why are you recording and running adding a pivot table to your sheet, when ONE IS ALREADY THERE?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jupops]

Hi the data changes from day to day and the pivot table is not there (at the moment), I needed to create two pivot tables on the sheet, which another sheet does a lookup so all the data is on a summery sheet (which also takes data from other work sheets).

 

[SkipVought]

the data changes from day to day

Makes no difference. Once you add a PT, all you have to do is REFRESH the PT as the source data changes.

I needed to create two pivot tables on the sheet

Unless this is a new workbook, no need to add but ONE TIME.

If you DO add more than one PT to a sheet via code, then be sure that EACH PT NAME IS UNIQUE.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jupops]

Thank you, I can't be;ieve how difficult I made it, thank you again

 

[SkipVought]

Glad you got a handle on it now.

This is why I often ask questions before offering an answer.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]