I would like to create a very simple pivot table using vba, but I keep getting errors with my pivot cache

Question

I have put together some VBA for a pivot table to basically just give how many CNs there are per person from the original table. Maybe there is a more simple way to do this, but I have to have code as I plan on adding this code to a macro. I have added a photo of my original table as well as what I am trying to have my pivot table look similar to. I get an error message on the [ With ws.PivotTables("PivotTable1").PivotFields("Type")] line: Unable to get the PivotFields property of the PivotTable class. Any ideas?

Example Table/pivot table photo Thanks!

Sub PivotTableTest2()

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

a very simple pivot table is not created using vba, it is created directly in Excel. — Luuk
It's best to avoid all uses of Range/Cells/etc without a qualifying worksheet object. For example Range("P1") should be ws.Range("P1") — Tim Williams

Excelosaurus Excelosaurus · Accepted Answer · 2020-08-06T20:34:35

If you add Option Explicit at the top of your module, and then go Debug / Compile, you will see that the compiler does not recognize keyword xlPivotTableVersion6. If you provide it, the Version argument of PivotCaches.Create must be part of the XlPivotTableVersionList enumeration. The Version argument is optional and I personally never provide it.

Option Explicit statement

Declaring Variables

PivotCache.Create method

XlPivotTableVersionList enumeration

I would like to create a very simple pivot table using vba, but I keep getting errors with my pivot cache

2 Answers