How do you automate the range on source data on a chart

[gautammalkani]

Hi

I would like to automate the range on source data on a chart. I would like to automate all the data and new data that keep getting added onto my worksheet so that the user does not need to edit the chart.

Here is what i have so far :

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 12/31/03 by MALKAG
'

'
Dim i As Integer
Dim k As Integer

i = 2

Do While Not IsEmpty(Range("C" & i).Value)
i = i + 1
k = i - 1
Loop
ActiveSheet.ChartObjects("Chart 45&quot.Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values= "='0603_WcUtil'!R2C5:RkC5"

'Note the last line is obviously wrong but how do i get the source data to be determined by a counter?


End Sub

Thanks for your help, time and suggestions.

Cheers

Gautam

 

[aMember]

I had to do a similar thing a while back.
I created a named range for my data. For arguments sake, let's assume my data was A1:C1. The source data for my chart pointed to this named range instead of cell references.

For data that was subsequently added to the worksheet which my chart was generated from, thru VBA I changed the named range to include the new columns.

I also had a "shift" in my chart. That is, let's say the named range included cells A1:Z1 but I only wanted to look at 5 at a time, I would set the named range to the 5 I wanted to look at.

Never had to change the series collection and found this to be much easier to deal with than the series collection. Found it quirky.

HTH,
Andrea