Pivot Table error - Tabular format
I'm trying to run a macro which will create a pivot table for me so that it ends up looking like this:
Header1 Header2 Sum of Units
text text 124
text text 8689
text text 36778
However, I'm getting the following error: 'unable to get the pivottables property of the worksheet class.' Does anyone know how to fix this?? Not sure what I'm doing wrong, very unfamiliar with pivot tables, especially when used in vba. Below is my current code:
Code:
Dim pg As PivotCache
Dim pj As PivotTable
With Sheets("Sheet1")
Set pg = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'" & .Name & "'!" & .Range("A1").CurrentRegion.Resize(, 3).Address(ReferenceStyle:=xlR1C1), _
Version:=xlPivotTableVersion14)
End With
Set pj = pg.CreatePivotTable(TableDestination:="", DefaultVersion:=xlPivotTableVersion14)
ActiveWorkbook.ShowPivotTableFieldList = True
With pj
With .PivotFields("Header1")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Header2")
.Orientation = xlRowField
.Position = 2
End With
.AddDataField .PivotFields("Units"), "Sum of Units", xlSum
End With
ActiveSheet.PivotTables("PivotTable3").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("PivotTable3").PivotFields("SEDOL").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable3").PivotFields("NAME").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable3").PivotFields("UNITS").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveWorkbook.ShowPivotTableFieldList = False
The row that is highlighted with the error is: ActiveSheet.PivotTables("PivotTable3").RowAxisLayout xlTabularRow
