Pivot table Code works if modified as its own macro but not unmodified. PLEASE HELP ME!!!

rkol297

rkol297

Board Regular
Joined
Nov 12, 2010
Messages
131
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'm confused,
I have a macro that builds a pivot table at the end of organizing and eliminating some rows and specific data basically a page formating marcro with some statments that delete rows if a value is present but that's not my area of issue.

I need to separate the pivot table code into it's own macro due to size limit on Excel Macro's. I know its rare to hit but I've done it as the setup macro does alot.

The reason I need to separate the pivot table code is that I need to make more pivot tables in addition to this first one but the code is too long and hits the limit.

Funny thing is if the first code is at the end of the setup macro it runs fine and makes the pivot table. If I separate it into its own I get, a Run Time '1004': Run Time 1004 Error: Unable to get the PivotTable property of the PivotField Class error. I get this error with this code:
Code: 
[FONT=Arial][COLOR=#000000]    Sheets("STATS-DATA").Select<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]'###PIVOT TABLE BUILDS###<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Range("A1").Select<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Sheets("STATS-DATA").Select<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Dim objTable As PivotTable, objField As PivotField<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    ActiveWorkbook.Sheets("STATS-DATA").Select<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Range("A1").Select<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Set objTable = Sheets("STATS-DATA").PivotTableWizard<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Set objField = objTable.PivotFields("MEDCO MAIL OR AOB")<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Set objField2 = objTable.PivotFields("TRC")<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.Orientation = xlColumnField<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField2.Orientation = xlColumnField<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField2.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "TRC").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "MEDCO MAIL OR AOB").Position = 2<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Set objField = objTable.PivotFields("DAY")<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        objField.PivotItems("FUTURE SHIP").Visible = True<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        objField.PivotItems("NEXT DAY").Visible = True<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        objField.PivotItems("PREVIOUS SHIP").Visible = True<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.Orientation = xlRowField<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "FUTURE SHIP").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "NEXT DAY").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "SAME DAY").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "PREVIOUS SHIP").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "SUNDAY SHIP").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "SATURDAY SHIP").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        Set objField = objTable.PivotFields("GROUPER")<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Dim varItemList() As Variant<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Dim strItem1 As String<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Dim i As Long<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Application.ScreenUpdating = False<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    varItemList = Array("REVA SUSP", "PAH INH", "PAH ORALS", "PAH INJ", "ZYMES", "HAE", "ALPHA-IG", "ACTI")<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    strItem1 = varItemList(LBound(varItemList))<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    With objTable.PivotFields("GROUPER")<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        .PivotItems(strItem1).Visible = True<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        For i = 1 To .PivotItems.Count<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            If .PivotItems(i) <> strItem1 And _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]                  .PivotItems(i).Visible = True Then<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]                .PivotItems(i).Visible = False<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            End If<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        Next i<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        For i = LBound(varItemList) + 1 To UBound(varItemList)<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            .PivotItems(varItemList(i)).Visible = True<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        Next i<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    End With<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.Orientation = xlRowField<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "ACTI").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "PAH ORALS").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "PAH INH").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "PAH INJ").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "ALPHA-IG").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "HAE").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "ZYMES").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.PivotItems( _<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]        "REVA SUSP").Position = 1<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Set objField = objTable.PivotFields("THERAPY TYPE")<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.Orientation = xlRowField<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Set objField = objTable.PivotFields("RX HOME ID #")<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.Orientation = xlDataField<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.Function = xlCount<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objField.NumberFormat = "Number"<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objTable.RowAxisLayout xlOutlineRow<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objTable.TableStyle2 = "PivotStyleMedium9"<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Sheets("Sheet12").Select<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    Sheets("Sheet12").Name = "STATS"<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    ActiveWorkbook.ShowPivotTableFieldList = False<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objTable.ShowTableStyleRowStripes = True<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objTable.ShowTableStyleColumnStripes = True<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objTable.MergeLabels = False<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]    objTable.PivotFields("GROUPER").ShowDetail = False

After hours of trying to figure out why this error occurs I am at my wits end so I started to chop it up until I got it to work when its separated into its own macro. Keep in mind what I've removed I need as it organizes the pivot table in the fashion that my company needs. The code below is very similar but I've taken out the parts of the code above that cause the error to occur.
Code: 
Range("A1").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("TRC")
    Set objField2 = objTable.PivotFields("MEDCO MAIL OR AOB")
    objField.Orientation = xlColumnField
    objField2.Orientation = xlColumnField
    Set objField = objTable.PivotFields("DAY")
    objField.Orientation = xlRowField
        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
    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
End Sub
[/COLOR][/FONT]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

rkol297
  • Question Question
Why do I get a Run Time '1004': Run Time 1004 Error: Unable to get the PivotTable property of the PivotField Class error
Replies
5
Views
2K
Andrew Poulsom
Andrew Poulsom
rkol297
  • Question Question
Macro Help Run Time 1004 Error:Unable to get the PivotTable property of the PivotField Class
Replies
3
Views
4K
par60056
P
rkol297
  • Question Question
How do I get code to continue if object is not present this time but could be next time??????
Replies
1
Views
547
JLGWhiz
JLGWhiz
A
  • Question Question
Issues with Macro to import data from web, create pivot table & filter it based on a dynamic range
Replies
4
Views
1K
thatmarine
T
rkol297
  • Question Question
Split up a huge macro using a call macro but pivot table code errors out????!
Replies
4
Views
629
Jerry Sullivan
Jerry Sullivan

Forum statistics

Threads
1,224,590
Messages
6,179,763
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top