[ms office - 2010] Working with Pivot Charts in Excel

[tombry]

Right, so I have this lovely excel file that does departmental breakdowns for the past three years, with next years projections. I have a sheet with about 10 Pivot Tables on it, that all feed charts.

 

I'm putting the charts into a word document with commentary below it, and I'd like to make it so that each department gets it's own section of the overall word document (giving it to SLT). I can embed the charts, but the problem is that whenever I change the charts for the other departments, all the charts in the word document change.

 

I'd like to know if anyone knows of a way to create chart sheets based off the filter - or if they have a macro that does it, as my only solution at this point is to give each department their own data/chart sheet, which kind of bloats out the excel file, and to be quite frank, is more work than I really want to do.

 

ideally I would only have to update the text in the word document, and the charts would update themselves as I updated the data in the back end tables.

 

anything?