PivotChart formatting: macro for multiple unique charts
 

Hi – I've used this group as a resource often over the last couple
years, but have never needed to post because someone else had always
already asked and gotten an answer to my question. Now I'm struggling
with a formatting issue using PivotCharts and would really appreciate
some insight. I'm creating a large number of different PivotCharts,
and I'd like to be able to format the series in them automatically.
My charts have different numbers of series on them, though they all
contain the same pool of series. What I'd like to be able to do is
tell Excel to format the individual series the same way on each
PivotChart – for example, Product X, which comes from state Y, is
color Z and was promoted by means A should always be represented by,
say, a purple triangle.

My data contains information on promotions carried out over the course
of the year by a list of retailers, and some retailers have locations
in more than one city. The individual PivotCharts will represent the
year's promotions for a retailer in a geographic region. There is one
page field, one data field, four series fields, and a set of category
fields that make up the year's dates by week.

I tried recording a macro for this, using the selection "All" in the
page field, but found that as soon as I changed any of the fields and
attempted to run the macro on a different chart, the macro gave me a
1004 runtime error that said, "Unable to get the Legend Entries
property of the legend class," and that the formatting did not stay
with the items I intended it to when I originally recorded it.

Is there a way I can do this? I'll warn you that unfortunately, I
have literally no Visual Basic experience – the closest I've gotten to
it is recording macros for other minor-league formatting issues. I
plan to start learning it soon, but would like to solve this problem
before then if possible.

TIA,
Jocelyn

PivotChart formatting: macro for multiple unique charts
 

While what you want might be possible, it doesn't seem easy. After
all, it requires that the software figure out the business significance
of a chart series.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi =3F I've used this group as a resource often over the last couple
years, but have never needed to post because someone else had always
already asked and gotten an answer to my question. Now I'm struggling
with a formatting issue using PivotCharts and would really appreciate
some insight. I'm creating a large number of different PivotCharts,
and I'd like to be able to format the series in them automatically.
My charts have different numbers of series on them, though they all
contain the same pool of series. What I'd like to be able to do is
tell Excel to format the individual series the same way on each
PivotChart =3F for example, Product X, which comes from state Y, is
color Z and was promoted by means A should always be represented by,
say, a purple triangle.

My data contains information on promotions carried out over the course
of the year by a list of retailers, and some retailers have locations
in more than one city. The individual PivotCharts will represent the
year's promotions for a retailer in a geographic region. There is one
page field, one data field, four series fields, and a set of category
fields that make up the year's dates by week.

I tried recording a macro for this, using the selection "All" in the
page field, but found that as soon as I changed any of the fields and
attempted to run the macro on a different chart, the macro gave me a
1004 runtime error that said, "Unable to get the Legend Entries
property of the legend class," and that the formatting did not stay
with the items I intended it to when I originally recorded it.

Is there a way I can do this? I'll warn you that unfortunately, I
have literally no Visual Basic experience =3F the closest I've gotten to
it is recording macros for other minor-league formatting issues. I
plan to start learning it soon, but would like to solve this problem
before then if possible.

TIA,
Jocelyn

PivotChart formatting: macro for multiple unique charts
 

Thanks for your input, Tushar. I'll keep looking!