Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically move data to another file | Excel Discussion (Misc queries) | |||
Automatically move data from one worksheet to another | Excel Discussion (Misc queries) | |||
How to move data from one sheet to another automatically | Charts and Charting in Excel | |||
move data in cells automatically | Excel Worksheet Functions | |||
how do i automatically move data from rows to columns? | Excel Discussion (Misc queries) |