generating Charts at run time

[Andrew XJ]

One of projects is to Copy one existing Charts and paste in another place in the same sheet of the source Charts. But i need to change the DataSource and SeriesCollection. I use ChartObjects(1) and to ChartObjects(2) to refer to the object source since they are the first two charts in the sheet. But when i Msgbox ChartObjects(1).Name, it is not the name as shown in the title when i click Chart Window. Why ?
And the data source is not changed even my codes have done that. ??? What are the possible problems when generating Charts.
Note: The charts are not Chart sheet. They are ChartObjects in a worksheet.

The data source is in another worksheet in the same workbook. When contents in data source sheet expands, the more charts will be generated by running a Macro.


regards
Andrew XJ

[Andrew XJ]

The following are parts of my code. Could you help me to find the problem?

ActiveSheet.ChartObjects(1).Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy

ActiveWindow.Visible = False
Windows(monthlySum).Activate
Range("A" & (10 * loopP - 5)).Select
ActiveSheet.Paste

ActiveSheet.ChartObjects(loopP).Activate
ActiveChart.SeriesCollection(1).XValues = "='Moving Average'!R" & (loopP * 6 - 1) & "C2:R" & (loopP * 6 - 1) & "C9"
ActiveChart.SeriesCollection(1).Values = "='Moving Average'!R" & (loopP * 6) & "C2:R" & (loopP * 6) & "C9"
ActiveChart.SeriesCollection(1).Name = "='Moving Average'!R" & (loopP * 6) & "C1"
ActiveChart.SeriesCollection(2).XValues = "='Moving Average'!R" & (loopP * 6 - 1) & "C2:R" & (loopP * 6 - 1) & "C9"
ActiveChart.SeriesCollection(2).Values = "='Moving Average'!R" & (loopP * 6 + 1) & "C2:R" & (loopP * 6 + 1) & "C9"
ActiveChart.SeriesCollection(2).Name = "='Moving Average'!R" & (loopP * 6 + 1) & "C1"

[Andrew XJ]

help...could anyone help me?
The Recorded Macro can work, how come i change the constant cells to variables and then can not work???