Pivot Table not being created by VBA Code

[paydog23]

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:

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.

 

[Zack Barresse]

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...

Set PT2 = PTCache2.CreatePivotTable(...

 

[mole999]

@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)