Creating a pivot table automatically
 

I have recorded the steps I used to create a pivot table.

If I then insert a blank worksheet and run the code, I get an error
"add fields method of pivot table class failed"

here's the code

CODE
ActiveWindow.SmallScroll Down:=-33
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll Down:=-9
Application.CommandBars("Stop Recording").Visible = False
ActiveWindow.SmallScroll Down:=-12
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll Down:=-15
ActiveWindow.SmallScroll ToRight:=22
ActiveWindow.LargeScroll ToRight:=-2
Range("D4:D5").Select
Range("D5").Activate
ActiveWindow.SmallScroll Down:=510
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll Down:=-12
Range("AD1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"TotalCombined!C1:C31").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable3"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array(
_
"Sheam Type", "Sheam Desc", "Data")
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("NOV05")
Orientation = xlDataField
Caption = "Sum of NOV05"
Position = 1
Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("DEC05")
Orientation = xlDataField
Caption = "Sum of DEC05"
Position = 2
Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("JAN06")
Orientation = xlDataField
Caption = "Sum of JAN06"
Position = 3
Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("FEB06")
Orientation = xlDataField
Caption = "Sum of FEB06"
Position = 4
Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("MAR06")
Orientation = xlDataField
Caption = "Sum of MAR06"
Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable3").PivotSelect "",
xlDataAndLabel

ActiveSheet.PivotTables("PivotTable3").Format xlClassicPivotTable
Application.CommandBars("PivotTable").Visible = False


does anyone know why this is not working?

in addition, where the name sof the data fields are mentioned, I want
the pivot to get the name of the field from the source data worksheet
in specified columns (row 1 of course)

can anybody help?


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=481944