Program pivot tables / charts

[graveyard]

hi people, just want to ask around. do you usually create pivot tables from VBA or do you put a standard pivot table on the spreadsheet so after getting the same data over, click refresh on the table to renew the data?

i am asking because as the few books i read on excel teach on programming pivot tables and charts and the way i do at work is to have the same template table which i keep refreshing with new data.

for off the fly data analysis, i will build pivots randomly depending on criteria as different data set requires different headers/column fields. so i would think ppl program pivots via vba only for standard data analysis where they know what to show in header, column but if thats the case since we are dealing with data that shld be presented in the same format, wouldnt putting the template and refreshing it works just as nicely as vba programming ?

Like to have views on this so I know the best practice. Thanks

 

[Jerry Sullivan]

Hi graveyard,

Since there haven't been any responses yet, here's my 2 cents...

I like using VBA for many things, but I haven't found the need to use VBA to create pivot tables.

My preference for creating pivot tables follows your description: For off the fly analysis- build a tailored PT; For reports that are reused- make a template file that includes PT's and just change the source data itself which is referenced using a Dynamic Named Range.

I can imagine a use for macros to create PT's that follow the same pattern in their layout and formatting but use different pivotfields for each pivot. A VBA function could take those fields as parameters and batch produce similar but different PT's that would be more time-consuming to do with a Template file. However, I haven't had the need for such a tool.

Maybe someone else can share examples where using a macro to create Pivots worked better for them than using a template file.

 

[graveyard]

Hi JS411, thank you for your response. I asked because I have seen almost every book on excel Ive seen talk about this and i am a huge fan of pivot table (any excel user can tell you how he/she adore pivots), makes slicing and dicing data much much easier. So i thought i may have missed out something, and that I would get more out of pivots than i already have if i were to use vba. Would be great if someone who uses vba to generate pivots share more on the advantage of such approach.