Excel Creating Pivot Table with VBA

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

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.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

Creating Pivot Table with VBA

Re: Creating Pivot Table with VBA