Updating chart sourcedata range in VBA

[tpmcavoy]

I am relatively new to VBA and I am having trouble updating the sourecdata for a chart in VBA. Recording a macro for it yeilded:

Sheets("Rating Chart").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Summary").Range("AA2:AB61"), _
PlotBy:=xlColumns

I want to dynamically determine how many rows of data I have and update the source range accordinly.

I locate the last row of data using:
FinalRow = Sheets("Summary").Cells(Rows.Count, 27).End(xlUp).Row

I then try to update the source range using the following:

ActiveChart.SetSourceData _
Source:=Sheets("Summary").Range(Cells(2, 27), Cells(FinalRow, 28)), _
PlotBy:=xlColumns

Using the Cells in the Range seems to be my problem but I don't know how to vary the range any other way. Any help would be greatly appreciated. Thank you.

 

[mvptomlinson]

Perhaps:

FinalRow = Sheets("Summary").Cells(Sheets("Summary").UsedRange.Rows.Count, 27).End(xlUp).Row

ActiveChart.SetSourceData Source:=Sheets("Summary").Range("AA2:AB" & FinalRow), PlotBy:=xlColumns

 

[wigi]

For instance,

ActiveChart.SetSourceData _
Source:=Sheets("Summary").Range("AA2:AB" & FinalRow), _
PlotBy:=xlColumns

 

[tpmcavoy]

Thank you. That was easy.

 

[Jon Peltier]

FWIW, the reason this didn't work

ActiveChart.SetSourceData _
    Source:=Sheets("Summary").Range(Cells(2, 27), Cells(FinalRow, 28)), _
    PlotBy:=xlColumns

is that VBA doesn't know what the Cells inside the parentheses refer to. This should work:

ActiveChart.SetSourceData _
    Source:=Sheets("Summary").Range(Sheets("Summary").Cells(2, 27), Sheets("Summary").Cells(FinalRow, 28)), _
    PlotBy:=xlColumns

 

[pr1asd]

Hi All,

Sorry, I know this is a old post. Just wanted to say that i have used this to update charts automatically.

Thank you.

I am also using application.ontime to schedule an update for a dashboard.

But I don't want the dashboard displayed all the time (or to pop up while I am working).

Is there anyway to automatically update the chart without Activating it?

Thanks.

PR1ASD

 

[RoryA]

Yes, you can refer to it using Charts("chart name") if it's a chart sheet, or sheets("sheet name").Chartobjects("chart name").Chart if it's embedded on a worksheet.

 

[pr1asd]

Thank you for the quick response, Rory.

worked like a charm.

thank you