Macro to create dynamic pie chart

[cikaze]

Hi everyone

I would really appreciate any help I can get with this.
Basically what I am trying to do is create a macro that will produce a pie chart that will update everytime new data is entered into the next row.
I have attached an example of my data

I can record a macro and create a pie chart but i would like the macro to show the most recent data every time it is run and obviously when you record a macro Excel fixes the data.

This is how far i have got, bu the more i fiddle with this the more confused i become

Sub test()

Dim ShName As String
With ActiveSheet
ShName = .Name
End With

Charts.Add
With ActiveChart
.ChartType = xlPie
.SetSourceData Source:=Range("K" & .Rows.Count).End(xlUp).Row
End With
.SeriesCollection(1).Name = "=""Sentiment: Twitter - BU"""
.SeriesCollection(1).XValues = "='INPUT'!$K$5:$M$5"
.SetElement (msoElementDataLabelBestFit)
.Location Where:=xlLocationAsObject, Name:=ShName

End With

End Sub


I know the .SetSourceData line is wrong but i'm not sure how to fix this?
If you have an entirely different code that would be welcome too.

[mrice]

You might be better using a dynamic named range approach to achieve your goal.

Have a look at

http://office.microsoft.com/en-gb/ex...001109801.aspx