Unable to get the PivotFields property of the PivotTable class


  • Hi all,

    I know I have seen this thread a few times but I don't seem to grasp the solution as each time it seems to be a little different.

    I have recorded a macro so I could use it in an automated spreadsheet, the code I have is;

        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "'INFO'!C1:C3").CreatePivotTable TableDestination:="", _
            TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
        ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
        ActiveSheet.Cells(3, 1).Select
        ActiveChart.Location Where:=xlLocationAsNewSheet
        With ActiveChart.PivotLayout.PivotTable.PivotFields("Type")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With ActiveChart.PivotLayout.PivotTable.PivotFields("Date")
            .Orientation = xlRowField
            .Position = 1
        End With
        ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
            PivotTable.PivotFields("Orders"), "Count of Orders", xlCount
        ActiveChart.PivotLayout.PivotTable.PivotFields("Count of Orders").Function = _

    It fails with the error in the subject when it gets to the following line;

     With ActiveChart.PivotLayout.PivotTable.PivotFields("Type")

    The spreadhseet is a simple one it has three columns, Date, order (Sum) and Type headings.

    Any help please , am using windows 2000 with excel 2002.




    Friday, October 20, 2006 1:41 PM