PDA

View Full Version : Automating pivot or static charts - bigger picture



tfrisch
07-07-2006, 10:29 AM
I am producing allot of reporting tools in excel via pivots and pivot charts. A big issue is the loss of all chart formatting whenever I refresh pivot charts.

So I decided I would record all my reformating and then just rerun. This worked ok until I saved as another work book or I changed order of sheets and charts within work book. Then get run errors and over my head.

Also I had someone send me some nice code on chart objects to help me learn the ropes. problem is I am not an expert.

FYI - very important point - I am not locked in to using pivot charts - if someone suggest using programmed static charts and getting the data from pivots into them I have no problem with that.

But hear are my top down questions to try and arrive at the best way to approach this chart automation.

1) Do I want to use vba to automate getting my desired chart formats back after refresh.

2) Where do I store the macros - in a another sheet like personal or in the workbook where charts are.

3) Do I click on the charts to make active to refresh - then how do I program active chart and what is an active chart?

4) Can I make a generic macro for liek charts that are 4 up on one page or are on 4 different pages.

5) Do I build macros or record just for my area's of interest or do I need to automate the entire chart process - all steps.

6) What is the best way to approach chart automation - are macro's created by simply recording build steps ok to use and transfer to other charts or are they buggy - I had allot fo problem moving them or the charts.

7) Can you just plug into a macro the parts of the chart you wish to alter and ghave it do routine on other parts. So how does one learn the parts.


8) Is there a place to go to see best way for chart automation.

Hey thnak you very much for your time - firsat time poster so let me know if can improve.

Todd

compariniaa
07-07-2006, 12:17 PM
i'm kind of new to vba, so i won't be able to answer all your questions

include your code so we can have a look at it.
also, regarding your formatting, right click the table and click table options. make sure "preserve formatting" is checked

tfrisch
07-07-2006, 12:24 PM
Thank you very much for reply - I will provide an organized example chart & code but forgive me for not being able to get to this before weekend. I will need a day or two to get back to you. Again sorry for the delay.

On a real bad deadline and wish to do example well and not rush it

Also I have oreserve formmatting checked and it still wipes it - I will provide example

compariniaa
07-07-2006, 12:47 PM
no problem, take your time. but, like I said, i'm not super experienced in VBA, so I can't really make any promises

matthewspatrick
07-08-2006, 07:23 PM
Todd,

I feel your pain. I too am frustrated that PivotCharts cannot "remember" the formatting I apply to them, and that they often revert to default settings on a refresh.

In the past, I have used VBA event subs to forcibly reapply the formatting I want.

tfrisch
09-29-2006, 11:15 AM
I was completely pulled away from this project and I mean completley so I wanted to get back to you and let you know I wa snto blowing this off. I may have to bump this topic a few lines down the list and adress some bigger issues but I do want to pursue this sometime soon.

shades
09-30-2006, 08:04 AM
You might want to check out the book by Bill Jelen , et al (some from this site), VBA and Macros for MS Excel. He provides code examples that create Pivot tables in VBA, which is vastly superior to the XL interface, more customizable, and gives you complete control over formatting. I don't have it with me right now (no, I don't have the book memorized), but I think it is chapter 11 or 12. Were I going to do extensive PivotTable reporting I would do it all in VBA.