I am not an expert in pivot  charts and I am sure someone else will be able to
help you more than I can (you may need to wait until it is Monday in the US
and they are back at work.!)


I would be fairly certain you do not need to generate a new chart  each time.
You need to change the columns and rows of the pivot table  which is used as
the source and then (probably) refresh the chart. You may need to change the
source definitions for the chart itself.

I do not have the time  to try to build a test application at the moment and,
just now, I do not have any charts  built on pivot tables I can experiment
with.

As I said, I am sure someone who is experienced in this stuff will help you.