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("D45").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?
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("D45").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?