Changing source data for an unactivated chart

Hi There,
I'm using a piece of code from a book that allows me to "name" a series range of an embedded chart on sheet 1(allowing me to modify it using VBA). It works great but only if I activate the chart. I'm going to be having many charts so I really want to find a way to change the series range without activating the chart(but still having the chart update). Here's the working code but if the chart gets deactivated for any reason, I get an error.

Public Sub test2()
Dim datarange As Range
dim k as integer

Worksheets("Sheet1").ChartObjects(1).Activate 'won't work wihout this line

For k = 1 To 20
Set datarange = ActiveSheet.Range("A1:A" & CStr(k))

Worksheets("Sheet1").ChartObjects(1). _
Chart.SeriesCollection(1).Values = datarange

'the above line gets the "unable to to set values property of series class" error when not activated

next k
End Sub

So...if it's possible to play with the series data ranges without activating the chart I would sure like know.
Thanks for any help.

Have you thought about using the echo function?

Application.Echo False
DoCmd.Hourglass True
***Make your update
DoCmd.Hourglass False
Application.Echo True

It may not be the same as trying for some code that updates without activating (which I don't know is possible), but it is an easier solution.

Thanks ,
John
"I may appear stupid, but I'm merely incompetent."