• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code to Create Pivot Table

H

Howardc

New Member
I would like VBA code to delete the current Pivot table sheets and to create two Pivot table sheets-see sheet "Purchases" & "FA-YTD Dep"


The Pivot Table-"Purchases" must contain the following:Asset Type, Capital Cost, financial Year


The Pivot Table FA-YTD Dep must contain the following:Asset Type, WDV, Capital Cost, total-dep


Your assistance in this regard is most appreciated
 
Have you tried recording a macro of you doing those actions? That would be the fastest way of generating the necessary code. After you have the recording, if needed, we can help you clean up the code by removing any extra stuff (like un-needed selections, scrolling, etc).
 
Hi Luke


I have tried to record a macro to create a Pivot Table for sheet purchases. The Pivot table was created perfectly when recording the macro (source data sheet “imported data”)


However, when running the macro a second time, it comes up with run time error 5 and the following code is highlighted


(ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _

"Imported Data!R1C1:R1048576C14", Version:=xlPivotTableVersion12). _

CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _

, DefaultVersion:=xlPivotTableVersion12)


It would be appreciated if you could assist. If you need me to email you my workbook, please forward me your email address
 
Hi, Howardc!

You can't create twice the same pivot table at the same place, that's why first time it runs Ok and second time it crashes. You have to change TableName and TableDestination, if you want to build may PT. If not, why running macro more than once?

Regards!
 
Hi SirJB7


Thanks for the reply. The reason I ran the macro twice was to ensure that it is working properly
 
Hi, Howardc!

Try selecting PT area manually and deleting it first, to perform your test, if only once or twice. If you're gonna do it frequently add this code first to the posted code:

-----

ActiveWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True

Selection.Delete Shift:=xlToLeft

-----

Regards!
 
You must log in or register to reply here.
Back
Top