Runtime Error '1004' - AddFields method of PivotTable class failed
 

Hi,

I can create a pivottable fine but when I record this to a macro and
try to run the macro I get an error on this line:

ActiveSheet.PivotTables("PivotTable3").AddFields
RowFields:=Array("STAGE", _
"SUB STAGE", "BUSINESS DESC", "Data"), ColumnFields:="EXPIRED"

Does anyone know what the solution to this is? (Note: I did a search
on this topic and found nothing)

Full macro code below:

Sub Create_PivotTable()
'
' Create_PivotTable Macro
' Macro recorded 21/05/2004 by MikeC
'

'
Sheets("TransferInData").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"TransferInData!C1:C8").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").AddFields
RowFields:=Array("STAGE", _
"SUB STAGE", "BUSINESS DESC", "Data"), ColumnFields:="EXPIRED"
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("BUSINESS
DESC")
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("PROCESSED")
.Orientation = xlDataField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields ("BUSINESS
DESC"). _
Orientation = xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Down:=12
End Sub

Regards,

MikeC

I've found a workaround
 

The workaround for recording a macro that creates a pivottable is
simple enough:

- Create a spreasheet with a pivottable pointing to the dataset
- Record a macro that will refresh the pivottable

I think that Excel has problems with the naming of the pivottable when
it tries to create it from scratch ie each time you run the macro,
Excel will name it PivotTable1, then PivotTable2,.. and so on.

I'll keep an eye out for any solutions to my problem still (not just
workarounds).

Thanks

(MikeC) wrote in message . com...
Hi,

I can create a pivottable fine but when I record this to a macro and
try to run the macro I get an error on this line:

ActiveSheet.PivotTables("PivotTable3").AddFields
RowFields:=Array("STAGE", _
"SUB STAGE", "BUSINESS DESC", "Data"), ColumnFields:="EXPIRED"

Does anyone know what the solution to this is? (Note: I did a search
on this topic and found nothing)

Full macro code below:

Sub Create_PivotTable()
'
' Create_PivotTable Macro
' Macro recorded 21/05/2004 by MikeC
'

'
Sheets("TransferInData").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"TransferInData!C1:C8").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").AddFields
RowFields:=Array("STAGE", _
"SUB STAGE", "BUSINESS DESC", "Data"), ColumnFields:="EXPIRED"
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("BUSINESS
DESC")
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("PROCESSED")
.Orientation = xlDataField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields ("BUSINESS
DESC"). _
Orientation = xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Down:=12
End Sub

Regards,

MikeC