Hi there,

I've tried searching for the syntax for my specific issue and so far have come up empty.

I'm trying to write a macro that will update all the charts in a given workbook, across multiple sheets. Each chart references a separate small table with 2 series. The data is currently monthly up to the end of 2012, but now we want to add the data for 2013 and update the charts accordingly. I'd also like to make this replicable so that in the future when we add data for 2014+ I can reuse the macro. Basically I'd just like to specify that the chart range should extend to the rightmost column in the table.

Here's what I have so far:

Sub chartupdate()
Dim Sh As Worksheet
Dim ChtObj As ChartObject

For Each Sh In ThisWorkbook.Worksheets
For Each ChtObj In Sh.ChartObjects
With ChtObj.Chart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
End With
With ChtObj.Chart.PlotArea.Select
.SetSourceData Source:=????
End With
Next ChtObj
Next Sh
End Sub

So the issue is that I can't figure out a way to specify a starting cell/range since each chart draws from a different table. It'd be great if I could figure out a way to designate the start row and then set the column as xlToRight or something like that. Or just expand the existing chart range to whatever is the rightmost column in the table.

Thanks!
Renee