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
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