PDA

View Full Version : Creating and deleting pivot tables and charts with VBA



craigwg
10-25-2010, 12:36 PM
I have a worksheet of data. I want to make a pivot chart (and therefore table) from the data with VBA. I have tried a few times and keep getting hung on this command:

ActiveWorkbook.Worksheets("NewPivot").PivotTables("PivotTable1"). _
PivotCache.createPivotTable TableDestination:="NewPivot!R1C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
When I record a macro and build the Pivot it records this line. I then try to run it again after deleting the table and chart but it crashes. Can you help me with this?

I need to build a macro that deletes the pivot chart and pivot table as well. I will fire these two macros on the Sheet_Activate and Shee_Deactivate commands.

How can I do this? I'm having a heck of a time with it! I've done it with regular charts in the past but the pivot chart is KILLING me!

Thanks