Graphing the sum of an autofilter result

Hi everyone,

I have data which is laid out as shown in the sample Sheet.jpg. The actual file contains many more categories. Because we need to create many graphs, I wrote a macro which allows the user to pick which data series they want to graph by, and creates a graph off preset chart options.

The macro works by autofiltering the for the appropriate column and field. It then selects that visible range as the chart source. This process is repeated for all the graphs selected by the user.

The macro works fine except when you have a data series that has more than one row. Let's say from my sample data, someone wanted a chart of Brian's total performance. My macro would autofilter for Brian, but it would end up with 3 rows as shown in FilteredResult.jpg. The resulting line graph obviously shows 3 separate lines. I would need some way to graph the entire result as one sum.

Keep in mind that the real data could contain upwards of 15 separate possible columns to group by. If I created sum rows, it could start getting messy depending on what the user selects. Especially if we are creating 30+ graphs.

Edit- I also just thought that maybe I could create a sum row at the end of the autofilter result, graph it, then delete the sum row. However, this would only work if there was some way in VBA to make the chart static and not update. Not sure if there is.