How can i change the series in chart based on pivot table?? please suggest..

Hi i am having a problem when plotting a chart using pivot table as a source.

Now my problem is, in my pivot table i have a column nested in a column. Say for every month i have another column nested which has 2 distinct values. so i have month and within every month i have billable and not billable.

Also, i have project in rows

Now, when i want to plot a column graph on billable and billable the range it is taking has 4 items in series (since i have only 2 months data), nov billable, nov not billable, dec billable and dec not billable. I dont want that!!

I want only two.. billable and not billable in series..
also, i want months in x - axis..

So basically, I dont want project (rows) in my chart. but Month(Column level1) in x-axis, and aggregated value of billable/not-billable as series.

I have no clue how to do it.. i have to do it using vba. i am trying to create a chart but am not able to..

Also, as i am trying again and again to achieve this, many times the chart automatically populates itself from the pivot table.. i am not setting the source to pivot table. i dont understand how it is working.. please provide an insight!!

Please help me!!

Welcome to the forum.

1. Please take a few minutes to read the Forum Rules about thread titles, and then edit yours to make it descriptive of your problem.

2. Why does it have to be done in VBA?

3. Post a workbook?

Mike, please don't post in threads with pending moderation.

We want to automate the creation of pivot table and chart based on the table. That is the reason i need to do it with VBA.

Do you mean like this?