How to get the PivotFields property of the PivotTable class?

Question

I have VBA code for a pivot table to give how many CNs per person from the original table.

I get an error message

Unable to get the PivotFields property of the PivotTable class

on the With ws.PivotTables("PivotTable1").PivotFields("Type") line.

Example of table and ideal Pivot Table

Dim ws As Worksheet
Dim wb As Workbook
Dim pc As PivotCache
Dim pt As PivotTable

Sheets("Supplier Quality").Activate

Set ws = ActiveSheet
Set wb = ThisWorkbook

Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.Range("Table2"))

Set pt = ws.PivotTables.Add(PivotCache:=pc, tabledestination:=ws.Range("P1"), TableName:="PivotTable1")
'
With ws.PivotTables("PivotTable1").PivotFields("Type")
    .Orientation = xlRowField
    .Position = 1
End With
With ws.PivotTables("PivotTable1").PivotFields("Task Owner2")
    .Orientation = xlColumnField
    .Position = 1
End With
ws.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Type"), "Sum of Tasks Overdue", xlSum
End Sub

Dy.Lee Dy.Lee · Accepted Answer · 2020-08-07T02:04:30

ws.Range("Table2") address is "a2:b28" . So, an error occurs because the range of the field is not included.
Other problems arise when you create a pivot table and then apply it to the pivot table again. So it is recommended to apply the refreshPivot code.

Sub makePivot()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim rngDB As Range
    
    Sheets("Supplier Quality").Activate
    
    Set ws = ActiveSheet
    Set wb = ThisWorkbook
    
    Set rngDB = ws.Range("a1").CurrentRegion
    
    'Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.Range("Table2"))
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngDB)
    
    Debug.Print ws.Range("Table2").Address '<~~  A2:B24   first range a2 so error occurs.
    
    Set pt = ws.PivotTables.Add(PivotCache:=pc, tabledestination:=ws.Range("P1"), TableName:="PivotTable1")

    
    
    With ws.PivotTables("PivotTable1").PivotFields("Type")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ws.PivotTables("PivotTable1").PivotFields("Task Owner2")
        .Orientation = xlRowField
        .Position = 1
    End With
    'ws.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Type"), "Sum of Tasks Overdue", xlSum
    Set pf = pt.PivotFields("Task Owner2")
    With pf
        .Orientation = xlDataField
        .Function = xlCount
        .Caption = "Sum of Tasks Overdue"
    End With
    'pt.AddDataField pf, "Sum of Tasks Overdue", xlCount

End Sub

refresh code

Sub refreshPivot()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim rngDB As Range
    
    Sheets("Supplier Quality").Activate
    
    Set ws = ActiveSheet
    Set wb = ThisWorkbook
    
    Set rngDB = ws.Range("a1").CurrentRegion
    
    'Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.Range("Table2"))
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngDB)
    
    
    'Set pt = ws.PivotTables.Add(PivotCache:=pc, tabledestination:=ws.Range("P1"), TableName:="PivotTable1")
    Set pt = ws.PivotTables("PivotTable1")
    With pt
        .ChangePivotCache pc
        .ClearTable '<~~ This must be done.
        .RefreshTable
    End With
    
    With ws.PivotTables("PivotTable1").PivotFields("Type")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ws.PivotTables("PivotTable1").PivotFields("Task Owner2")
        .Orientation = xlRowField
        .Position = 1
    End With

    Set pf = pt.PivotFields("Task Owner2")
    With pf
        .Orientation = xlDataField
        .Function = xlCount
        .Caption = "Sum of Tasks Overdue"
    End With

End Sub

How to get the PivotFields property of the PivotTable class?

1 Answers

refresh code