Macro Help Run Time 1004 Error:Unable to get the PivotTable property of the PivotField Class

[rkol297]

I get the error Run Time error '1004': Unable to get the PivotTable property of the PivotField Class when I attempt to run the following code. However if I take this code and put it at the end of a macro that simply just formats the columns and rows prior to this macro it will generate the pivot table but If I try and run this by itself It gives me the mentioned error. specifically it errors out when it reaches.
objField2.PivotItems( _
"TRC").Position = 1

How do I get this to be its own macro separate from the first?

Sub RSLDASHBOARDADVANCED_PIVB()
   With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
        .EnableEvents = False
    End With
    Range("A1").Select
'###STATS PIVOT TABLE###
    Sheets("STATS").Select
    Dim objTable As PivotTable, objField As PivotField
    ActiveWorkbook.Sheets("STATS").Select
    Range("A1").Select
    Set objTable = Sheets("STATS").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 = "TOTAL STATS"
    ActiveWorkbook.ShowPivotTableFieldList = False
    objTable.ShowTableStyleRowStripes = True
    objTable.ShowTableStyleColumnStripes = True
    objTable.MergeLabels = False
    objTable.PivotFields("GROUPER").ShowDetail = False
    Range("A1").Select
        With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

 

[par60056]

I do not think you are getting a pivot table object back.

Check that objTable is not nothing after you set it.

 

[rkol297]

What do you mean that its not nothing? See the thing is this code works if its attached to the main macro but if I try and run it separately it errors out. I need to cut the first macro at this point because I hit the size limit for the file and I need to write more code.

 

[par60056]

I am always learning new things here. I found out that some method calls have undocumented side effects.

So it is possible that I am wrong. I have never created a pivot table by calling "PivotTableWizard" and looking at the docs, I don't know how it would get any of the information it needs to build the pivot table without any parameters. After stepping past this line in the code, you should be able to do a quick watch on the objTable and make sure that it is pointing to an object.

I don't see a DIM for objField2.

I believe you that there is a size limit for the code but I have never hit it. I break things into smaller functional processes and then have something that calls them all.

I looked at the main macro code that you had posted the first time and saw that there was a ton of things that you could clean up.

There is code in there to scroll the screen around. Every thing is done by selecting cells (this really slows things).

This bothers me a little:

    Sheets("Sheet12").Select 
   Sheets("Sheet12").Name = "TOTAL STATS"

How do you know the sheet will be called "Sheet12"? why do you select it? Why not just rename it with :
ActiveSheet.name = "TOTAL STATS"