VBA code to change a column graph to a line graph, within a pivot table/ graph

[Bern23]

HI All</SPAN>

I have a pivot chart (column graph) which is linked to pivot data, and I want one series of the pivot graph to be a line graph with the rest of the series remaining as a column graph.
</SPAN>
This is easy enough; I simply changed the chart type for the specific series to a line graph.
</SPAN>
However, when the pivot table refreshes, the line graph reverts back to a column graph. I have tried searching for a solution for this, but it turns out that this is a bug in Excel and the workaround is to have VBA code convert the series data from a column graph back to a line graph</SPAN>

So I am trying to create a macro that will automatically convert that specific series to a line graph and I will assign that macro to a button in my spreadsheet.
</SPAN>
This would be easy enough but the series position in the pivot table changes based on what series are selected in the pivot table, and I am having trouble writing the macro.</SPAN>

I hope that the above makes sense. Can someone please help? Some more specific information about the data and VBA code follows.</SPAN>
This is a copy of the Pivot table</SPAN>

Sum of Value</SPAN></SPAN>	Column Labels</SPAN></SPAN>
Row Labels</SPAN></SPAN>	2010</SPAN></SPAN>	2011</SPAN></SPAN>	2012</SPAN></SPAN>	2013</SPAN></SPAN>	Target</SPAN></SPAN>
Jan</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Feb</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Mar</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Apr</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
May</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Jun</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Jul</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Aug</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Sep</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Oct</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Nov</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Dec</SPAN></SPAN>	1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	5</SPAN></SPAN>	10</SPAN></SPAN>
Grand Total</SPAN></SPAN>	12</SPAN></SPAN>	24</SPAN></SPAN>	36</SPAN></SPAN>	60</SPAN></SPAN>	120</SPAN></SPAN>

<TBODY>
</TBODY>

I want the last column “Target” to be the series of data that is always a line chart, with the other series remaining as column charts.</SPAN>
So when “Target” is in column 6 of the pivot table, the VBA code to change the series to a line chart is fairly straight forward.</SPAN>

However, I am using a slicer to filter the series data in the pivot graph. So the macro does not work when “Target” is not in Column 6, for example because the data is filtered and “Target” is now in Column 3.</SPAN>

My VBA code at the moment looks like this:</SPAN>

Sub Test ()
With ActiveWorkbook.SlicerCaches("Slicer_Year")</SPAN>​

If .SlicerItems("Target").Selected = True Then</SPAN>​

ActiveSheet.ChartObjects("Chart 6").Activate</SPAN>
ActiveChart.SeriesCollection(6).Select</SPAN>
ActiveChart.SeriesCollection(6).ChartType = xlLine</SPAN>​

End If</SPAN>​

End With
End Sub
</SPAN>​

I can see the problem in my code but I don’t know how to fix it. It is specifically referencing the column for “Target” in the pivot table which it thinks will always be in Column 6, and does not take into account that "Target" can be in another column in the pivot table.
What would the VBA code be to make “Target” a variable so that this could work?</SPAN>

I hope this makes sense and that someone can help with this</SPAN>
Thanks</SPAN>
B</SPAN>

 

[Andrew Poulsom]

You can use the name of the Series as well as a number as the SeriesCollection Index.

 

[Bern23]

Briliant! Thanks alot Andrew
I had actually tried that earlier but didnt seem to work, but realised my mistake - i had left out the inverted commas in the series name.
Instead of
ActiveChart.SeriesCollection(Target).Select

It should be:
ActiveChart.SeriesCollection("Target").Select</SPAN>

And it so far works
Thanks alot for the help!!
</SPAN>