PDA

View Full Version : [SOLVED] Pivot Table error - Tabular format



roxnoxsox
11-17-2015, 04:07 AM
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:


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

Aflatoon
11-17-2015, 05:02 AM
You should be using pj and not ActiveSheet.PivotTables("PivotTable3")

roxnoxsox
11-19-2015, 04:40 AM
Oh dang it, thank you!!