Pivot Table not being created by VBA Code

paydog23

New Member
Joined
Jul 12, 2017
Messages
28
Office Version
  1. 365
Platform
  1. Windows
I have been struggling to create a pivot table using the following piece of VBA code, but I cannot find the error. I assigned a watch window to PT2, and I noticed after I set PT2 equal to the PivotCache, PTCache2, PT2 still appears with a value of nothing. The With Block does not work because VBA thinks PT2 is an empty object:

VBA Code:
Sub GroupDetectsPivot()
    Dim PTCache2 As PivotCache
    Dim PT2 As PivotTable 'GroupDetectsPivot PivotTable
    
'Create the cache for the ChemicalDetectsPivot Pivot Table
        Set PTCache2 = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:="GroupDetects")  'GroupDetects table on the Detects sheet

    Sheets("DetectsPivot").Activate
    Set PT2 = ActiveSheet.PivotTables.Add( _
        PivotCache:=PTCache2, _
        TableDestination:=Range("F1"), _
        TableName:="GroupDetectsPivot") 'Name the pivot table
        
'Set up GroupDetectsPivot to count detects by group
    With PT2
    .ColumnGrand = False 'Removes grand total row
    .RowGrand = False 'Removes grand total column
    .NullString = "0" 'Shows blank values as 0s
    
    'Add Detects page filter
        With .PivotFields("Detects")
                .Orientation = xlPageField
                .Position = 1
                .PivotItems("ND").Visible = False 'Hide ND findings
                .EnableMultiplePageItems = True 'Allow multiple selections
        End With
    'Add Group field
        With .PivotFields("Group")
            .Orientation = xlRowField
            .Caption = "Group"
            .ShowAllItems = True
        End With
        
      'Add Detects field and rename as "Count of Detects"
        With .PivotFields("Detects")
            .Orientation = xlDataField
            .Caption = "Count of Detects"
        End With
        
        With .PivotFields("Group")
                .AutoSort xlDescending, "Count of Detects"
                .PivotItems("SUM (PFOS + PFOA)").Visible = False 'Hide the sum of PFOS+ PFOA from the pivot table
        End With
        
         'Add the quarter data as a column
        With .PivotFields("Quarter")
            .Orientation = xlColumnField
            .Caption = "Quarters"
        End With
        
    End With
   
End Sub

I am pulling data from a table, GroupDetects, on another sheet, Detects. I am using Excel 2016.
 

Attachments

  • PT2 Watch Window.png
    PT2 Watch Window.png
    7.2 KB · Views: 20

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Got a sample file? Without anything to test, have you tried using 'CreatePivotTable' off the cache instead of 'Add' from the PivotTables object? Otherwise, off the top of my head, it looks ok, if nothing else syntactically. Perhaps...
VBA Code:
Set PT2 = PTCache2.CreatePivotTable(...
 
Upvote 0
@paydog23
Your post has been reported as being cross posted with another site.

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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