MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Creating Pivot Table with VBA


Posted by Skip Bisconer on December 04, 2000 8:50 PM


I have been trying to make this VBA code work for me to create a Pivot table. Can some one tell me why I error out and SET PT here is the code I'm tring to make work. The .PivotFields are all on one line, they just word wrap in this box.

Dim PTCache As PivotCache
Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)
****" This is the problem area"*****

Set PT = PTCache.CreatePivotTable _
(TableDestination:="", _
TableName:="PivotTable1")

With PT
.PivotFields("CallCode").Orientation = xlPageField
.PivotFields("GraphCode").Orientation = xlColumnField
.PivotFields("Date").Orientation = xlRowField
.PivotFields("GraphCode").Orientation = xlDataField

End With


Posted by Andy Ashman on December 05, 2000 1:48 AM

I don't know anything about Pivot tables or automating them, but a quick look in the help files reveals that the PivotCaches collection does not have an "Add" method so that line will never work.