Why do I get a Run Time '1004': Run Time 1004 Error: Unable to get the PivotTable property of the PivotField Class error

[rkol297]

Why do I get a Run Time '1004': Run Time 1004 Error: Unable to get the PivotTable property of the PivotField Class error? When I attempt to build a pivot table using the following code:

'###PIVOT TABLE BUILDS###
    Range("A1").Select
    Sheets("STATS-DATA").Select
    Dim objTable As PivotTable, objField As PivotField
    ActiveWorkbook.Sheets("STATS-DATA").Select
    Range("A1").Select
    Set objTable = Sheets("STATS-DATA").PivotTableWizard
    Set objField = objTable.PivotFields("MEDCO MAIL OR AOB")
    Set objField2 = objTable.PivotFields("TRC")
    objField.Orientation = xlColumnField
    objField2.Orientation = xlColumnField
    objField2.PivotItems( _
        "TRC").Position = 1
    objField.PivotItems( _
        "MEDCO MAIL OR AOB").Position = 2
    Set objField = objTable.PivotFields("DAY")
        objField.PivotItems("FUTURE SHIP").Visible = True
        objField.PivotItems("NEXT DAY").Visible = True
        objField.PivotItems("PREVIOUS SHIP").Visible = True
    objField.Orientation = xlRowField
    objField.PivotItems( _
        "FUTURE SHIP").Position = 1
    objField.PivotItems( _
        "NEXT DAY").Position = 1
    objField.PivotItems( _
        "SAME DAY").Position = 1
    objField.PivotItems( _
        "PREVIOUS SHIP").Position = 1
    objField.PivotItems( _
        "SUNDAY SHIP").Position = 1
    objField.PivotItems( _
        "SATURDAY SHIP").Position = 1
        Set objField = objTable.PivotFields("GROUPER")
    Dim varItemList() As Variant
    Dim strItem1 As String
    Dim i As Long
    Application.ScreenUpdating = False
    varItemList = Array("REVA SUSP", "PAH INH", "PAH ORALS", "PAH INJ", "ZYMES", "HAE", "ALPHA-IG", "ACTI")
    strItem1 = varItemList(LBound(varItemList))
    With objTable.PivotFields("GROUPER")
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 And _
                  .PivotItems(i).Visible = True Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) + 1 To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
    End With
    objField.Orientation = xlRowField
    objField.PivotItems( _
        "ACTI").Position = 1
    objField.PivotItems( _
        "PAH ORALS").Position = 1
    objField.PivotItems( _
        "PAH INH").Position = 1
    objField.PivotItems( _
        "PAH INJ").Position = 1
    objField.PivotItems( _
        "ALPHA-IG").Position = 1
    objField.PivotItems( _
        "HAE").Position = 1
    objField.PivotItems( _
        "ZYMES").Position = 1
    objField.PivotItems( _
        "REVA SUSP").Position = 1
    Set objField = objTable.PivotFields("THERAPY TYPE")
    objField.Orientation = xlRowField
    Set objField = objTable.PivotFields("RX HOME ID #")
    objField.Orientation = xlDataField
    objField.Function = xlCount
    objField.NumberFormat = "Number"
    objTable.RowAxisLayout xlOutlineRow
    objTable.TableStyle2 = "PivotStyleMedium9"
    Sheets("Sheet12").Select
    Sheets("Sheet12").Name = "STATS"
    ActiveWorkbook.ShowPivotTableFieldList = False
    objTable.ShowTableStyleRowStripes = True
    objTable.ShowTableStyleColumnStripes = True
    objTable.MergeLabels = False
    objTable.PivotFields("GROUPER").ShowDetail = False

The code is selecting the correct data and the Sheet would be Sheet 12 as the workbook has several sheets already.

 

[Andrew Poulsom]

Why are you using the PivotTableWizard method here?

Set objTable = Sheets("STATS-DATA").PivotTableWizard

Does it work if you use?

Set objTable = Sheets("STATS-DATA").PivotTables(1)

 

[rkol297]

It returns the error Run Time '1004': Run Time 1004 Error: Unable to get the PivotTables property of the Worksheet Class.

Highlighting your code:

Set objTable = Sheets("STATS-DATA").PivotTables(1)

 

[Andrew Poulsom]

That means you don't have a pivot table on that worksheet.

 

[rkol297]

But thats why I was using the wizard as it creates a pivot table on that sheet, do you know another way to create a pivot table with a macro?

 

[Andrew Poulsom]

Actually your code works for me with some dummy data. What data do you have on sheet STATS-DATA? Maybe you need to use the PivotTableWizard's SourceData argument. It's unusual to see it with no arguments.