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:
I am pulling data from a table, GroupDetects, on another sheet, Detects. I am using Excel 2016.
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.