Announcement

Collapse
No announcement yet.

Unable to Get The PivotFields Property of PivotTable Class

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Unable to Get The PivotFields Property of PivotTable Class

    I recorded the following code using the 'Record Macro' option. The macro does not work properly when I run it and I receive the following error message:
    Runtime Error: '1004' Unable to get the PivotFields property of PivotTable class

    The macro only pulls in the PivotFiled- 'Result' into the Pivot Tables dialog box, it gives me an error when it tries to pull the 'Part#' and 'Date' fields.

    Thanks in advance

    Code:
    Cells.Select
        Selection.Copy
        Sheets.Add
        ActiveSheet.Paste
        Sheets("Sheet1").Select
        Sheets("Sheet1").Name = "Reporter"
        Range("D1").Select
        Application.CutCopyMode = False
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "Reporter!C1:C3").CreatePivotTable TableDestination:=Range("D1"), TableName _
            :="PivotTable1"
        With ActiveSheet.PivotTables("PivotTable1")
            .NullString = "0"
            .RowGrand = False
            .SmallGrid = False
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("PART#")
            .Orientation = xlRowField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("RESULT")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("DATE")
            .Orientation = xlDataField
            .Position = 1
        End With
    Last edited by Dave Hawley; November 29th, 2006, 11:40.

  • #2
    Re: Runtime Error1004: Pivot Tables

    It looks like your source data is just one column
    Code:
    SourceData:= "Reporter!C1:C3"
    Do you have the result in column C ? Try extending the range .

    HTH
    Carl
    locii Insight
    Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

    Comment


    • #3
      Re: Runtime Error1004: Pivot Tables

      I can't believe I missed that... I manually changed the range to refer to Columns A to C in line 2 below--->

      Code:
      ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
          "Sheet13!A:C").CreatePivotTable TableDestination:="", TableName:= _
              "PivotTable2"
      Funny thing is, when I record the Macro; even though I select the columns A:C, it still records it as C1:C3.

      I've learned my lesson. Thank you v. much.
      Last edited by Dave Hawley; November 29th, 2006, 11:40.

      Comment

      Working...
      X