Macro to update chart range to new last column

[linesout]

Hi all,

I've found this forum enormously helpful so far, but am getting stuck on an issue. I've inherited a workbook that contains multiple worksheets, each of which have a bunch of data series and under each table is a chart of that table. The tables (just regular tables, not of the Insert Table variety) all contain three rows of relevant data - the months between 1/1/2010 and 1/1/2013, and two series over that time span. My task is to update the charts that reference these tables to include new data that now exists out to 1/1/2014.

I've been trying to figure out how to use the ChartObject method to achieve this, but the .SetSourceData option seems like you need to specify a range. However, I'm not sure how to get each chart to access its existing range, and then shift the column value to the desired last column. I don't mind just saying the last column is "AW" in this case and updating it as needed, or using the equivalent of Ctrl+Shift+Right (xlRight?) to get to the last value.

I've found this bit of code on this site that I think is a useful approach:

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
    Next ChtObj
Next Sh


End Sub

Based on what I've read I'd like to add some kind of With ChtObj.Chart.SetSourceData sequence, but again, at that point I have to specify the range of the chart right? And there are like 50 tables in each sheet off which the 50 charts are based. So I'm not sure how the macro would know where it is positionally; once it gets to the Next ChtObj how does it know what its current data range is, and how can I preserve that range while only changing the column boundary of it? Is there a way to specify a variable like CurrentRange once the chart object is selected, then get the rows out of it using .Row, and set the columns as A and AW?

Hopefully this made sense. Would really appreciate any input!!
Thanks,
Renee