Split up a huge macro using a call macro but pivot table code errors out????!

[rkol297]

I'm using a CALL Macro to split up a HUGE macro into different pieces:

Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
    Dim APPSPD As Worksheet
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
        .EnableEvents = False
    End With
Call SHEETSETUP
Call SPECIFICFUNCTIONS
Call STATSPIVOT
Call AOBRSLPIVOT
Call MEDCORSLPIVOT
Call IGMEDCOZEROVARIABLEFILLLISTPIVOT
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:

    objField2.PivotItems( _
        "TRC").Position = 1
    objField.PivotItems( _
        "MEDCO MAIL OR AOB").Position = 2

Keep in mind when this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well if that helps.

Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
 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", "A-P-T")
    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( _
        "A-P-T").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("Sheet9").Select
    Sheets("Sheet9").Name = "WORKLOAD STATS"
    ActiveWorkbook.ShowPivotTableFieldList = False
    objTable.ShowTableStyleRowStripes = True
    objTable.ShowTableStyleColumnStripes = True
    objTable.MergeLabels = False
    objTable.PivotFields("GROUPER").ShowDetail = False
    Range("A1").Select
End Sub

 

[Jerry Sullivan]

Hard to say for certain without testing, but if it worked before breaking out the Subs but not after, it could be because the datatype of objField2 is not Declared in STATSPIVOT. It might be declared in one of the earlier calls, but that would not have scope inside this Sub.

Try adding to STATSPIVOT...

Dim objField2 As PivotField

 

[rkol297]

still errors out stating Run-Time Error '1004':
Unable to get the PivotItems property of the PivotField class

I am completely stumped this macro works if it is not broken up into several different little macros but I have to break it up as its too big to be only on large macro anymore.

 

[Jerry Sullivan]

When the code breaks in the debugger is it just highlighting the first statement at the point you showed?

objField2.PivotItems("TRC").Position = 1

Try examining that object in the immediate window of the VBE by trying to get a few properties. Try entering each of these statements into the window:
?objField2.Name

?objField2.PivotItems("TRC").Name

?objField2.PivotItems("TRC").Position

The responses should help narrow down the cause of the problem.

 

[Jerry Sullivan]

To clarify, you should enter those statements in the Immediate Window when the code breaks in the debugger so we can examine the state of the properties at that point. Don't hit Reset or it will clear the values.