Creating a pivot table automatically

M

matpj

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:=xlDatabase, 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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top