Macro to automatically move chart data
 

Hi Everyone
I am trying to automate a excel spreadsheet with a chart. There is one
table with the week and data fields. The chart shows data for the last ten
weeks. Every week a new line is added to the data table at the bottom with
the new week and new data. Right now I have to click on the chart and then
move the selection in the data part one line down to include the new data. Is
there a way to automate this.
Thank You

Macro to automatically move chart data
 

Some more info in this. In the macro here is one Line

ActiveChart.SeriesCollection(2).XValues = "=TTE'!R63C2:R76C2
ActiveChart.SeriesCollection(3).XValues = "=TTE'!R63C2:R76C2

I want the values R63C2:R76C2 to change to R64C2:R77C2 and so on for every
new week. Any help would be appreciated.

Macro to automatically move chart data
 

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.

Macro to automatically move chart data
 

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.

Macro to automatically move chart data
 

Hi

Here is the macro which will resize the serises by one row per time we run
the macro.

Hope this helps.

Sub Test()
Sheet1.ChartObjects("Chart 1").Activate
Dim ss As Series
Dim strs() As String
Set ss = ActiveChart.SeriesCollection(1)
strs = Split(ss.Formula, ",")
Dim rg As Range
Set rg = Range(strs(2))
Set rg = rg.Resize(rg.Rows.Count + 1)
ActiveChart.SeriesCollection(1).Values = rg

Set ss = ActiveChart.SeriesCollection(2)
strs = Split(ss.Formula, ",")
Set rg = Range(strs(2))
Set rg = rg.Resize(rg.Rows.Count + 1)
ActiveChart.SeriesCollection(2).Values = rg
End Sub

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.

Macro to automatically move chart data
 

Thank you Peter
This gives me the baseline to get this to work.


""Peter Huang" [MSFT]" wrote:

Hi

Here is the macro which will resize the serises by one row per time we run
the macro.

Hope this helps.

Sub Test()
Sheet1.ChartObjects("Chart 1").Activate
Dim ss As Series
Dim strs() As String
Set ss = ActiveChart.SeriesCollection(1)
strs = Split(ss.Formula, ",")
Dim rg As Range
Set rg = Range(strs(2))
Set rg = rg.Resize(rg.Rows.Count + 1)
ActiveChart.SeriesCollection(1).Values = rg

Set ss = ActiveChart.SeriesCollection(2)
strs = Split(ss.Formula, ",")
Set rg = Range(strs(2))
Set rg = rg.Resize(rg.Rows.Count + 1)
ActiveChart.SeriesCollection(2).Values = rg
End Sub

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.

Macro to automatically move chart data
 

Hi

You are welcome.
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.