Dynamic Chart Updating

[troy_lee]

Column A on a datasheet is the week number. This is the chart's X axis. Column C is the value that gets plotted on the Y axis.

I want to make the chart dynamic so that every week when I add a new row, the chart's data range not only expands to include the new row but also readjusts the first row of the range to be one more than the previous week. In this manner I would always have only 52 weeks graphed on the chart.

Thanks in advance for the help.

Troy

 

[Georgiboy]

Something like this maybe...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim EndRow As Integer
Dim AddR As String

EndRow = Range("A" & Rows.Count).End(xlUp).Row

If Target.Column < 4 Then
    AddR = Target.Address
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!$A$" & EndRow - 51 & ":$A$" & EndRow
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!$C$" & EndRow - 51 & ":$C$" & EndRow
End If

Range(AddR).Select

End Sub

it would need to go in the sheet code window.
if you change any cell in a column <4 it will update a chart "Chart 3" in this case.

Hope this helps

 

[troy_lee]

Thanks for the help. I forgot to mention that my data is on one sheet and my chart on another. What do you recommend I change the ActiveSheet object to to refer to the sheet with the chart. The chart's sheet name is Scorecard.

Thanks.

 

[Georgiboy]

Yes give it a go, change the activesheet part to sheets("Sheet1) and change the chart name.

 

[troy_lee]

Here is my code with added parameters from my original question. I am getting a failure "Unable to set the values of the series class." Here is the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim EndRow As Integer
Dim AddR As String
Dim chart As chart

EndRow = Range("A" & Rows.Count).End(xlUp).Row

If Target.Column < 8 Then
    AddR = Target.Address
    Worksheets("Scorecard").ChartObjects("YTY").Activate
    'ActiveChart.SeriesCollection(1).XValues = "=Data!$A$" & EndRow - 51 & ":$A$" & EndRow
    ActiveChart.SeriesCollection("Shipped").Values = "=Data!$C$" & EndRow - 51 & ":$C$" & EndRow
    ActiveChart.SeriesCollection("Received").Values = "=Data!$G$" & EndRow - 51 & ":$G$" & EndRow
    ActiveChart.SeriesCollection("On Hand").Values = "=Data!$I$" & EndRow - 51 & ":$I$" & EndRow
End If

Range(AddR).Select

End Sub

I also have a question about referencing the X values properly. I can't see where I can assign it a name like the other series so I assume its index number is (1).

Thanks for the help.