Paste and Alter Charts in VBA

[Bazman76]

Hi there,

I want to create and alter several charts

By recording a macro I think I am nearly there.

However this line does not work?

ActiveSheet.ChartObjects("Chart" & i).Activate

Can someone please advise

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 05/07/2011 by HP
'

'
For i = 5 To 10
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False
Windows("RBS_USD_data2.xls").Activate
'Range("L32").Select
Cells(34, 2).Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart" & i).Activate
ActiveChart.SeriesCollection(1).XValues = "=Sheet2!R839C1:R1548C1"
ActiveChart.SeriesCollection(1).Values = "=Sheet2!R839C2:R1548C2"
ActiveChart.SeriesCollection(2).XValues = "=Sheet2!R839C1:R1548C1"
ActiveChart.SeriesCollection(2).Values = "=Sheet2!R839C9:R1548C9"
Next i
End Sub

 

[VoG]

Try with a space

ActiveSheet.ChartObjects("Chart " & i).Activate

 

[Bazman76]

Try with a space

ActiveSheet.ChartObjects("Chart " & i).Activate

I think thats probably it but now it appears I have another problem.

Even though I have been deleting all these new charts I have been creating while testing. It seems that when a new chart is pasted its no longer "Chart 5". How can I find out what a freshly pasted chart is called?

 

[VoG]

Try

ActiveSheet.ChartObjects(ChartObjects.Count).Activate

 

[Bazman76]

no gives me the error

run-time error '424'

object required

I include the ful macro in case I have incorporated your code incorrectly

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 05/07/2011 by HP
'

'
For i = 5 To 10
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False
Windows("RBS_USD_data2.xls").Activate
'Range("L32").Select
Cells(34, 2).Select
ActiveSheet.Paste
ActiveSheet.ChartObjects(ChartObjects.Count).Activate
'ActiveSheet.ChartObjects("Chart 5").Activate
'ActiveSheet.ChartObjects("Chart " & i).Activate
ActiveChart.SeriesCollection(1).XValues = "=Sheet2!R839C1:R1548C1"
ActiveChart.SeriesCollection(1).Values = "=Sheet2!R839C2:R1548C2"
ActiveChart.SeriesCollection(2).XValues = "=Sheet2!R839C1:R1548C1"
ActiveChart.SeriesCollection(2).Values = "=Sheet2!R839C9:R1548C9"
Next i
End Sub

 

[VoG]

Sorry, it should be

ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Activate

 

[Bazman76]

Sorry, it should be

ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Activate

Thank you VoG that's it!!

This forum is fantastic!!!!!!!!!!!!!!!!