Hi Peter
Thanks for you input. One Cloumn of the table does have the date. I still
dont know how the series selection will change to the next row.

Previous week: ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C1:R20C1"

Next week: ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C1:R21C1"

Columns in the file are
Date(firday of the week) No.of calls.

Thanks for your help.




""Peter Huang" [MSFT]" wrote:

Hi

I think we can handle the code below in the Workbook_Open macro.

Private Sub Workbook_Open()
Sheet1.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C1:R20C1"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R1C2:R20C2"
End Sub

To run the macro weekly, I think we need to check some flag to see when we
need to update the SeriesCollection.
e.g. If the table in the sheet has one column which will indicate the time,
we can use that one to judge.
Or we can stored the last update data certain cell and check the cell's
date with current date to see if it has been 1 week.

If you still have any concern, please feel free to post here.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.