Hi there, brand new to forum and just learning VBA.
I have a table of data, over 100 entries, each needing a separate chart. I have recorded a macro which does the following
Selects my chart
changes its name
changes the row values it references
copies to cell on the sheet, 16 rows down.
I have done it in the macro for 3 repetitions but can someone guide me how to loop for 100 repetitions but also increment the chart name and reference data?
Sub Macro2()
'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Copy
Range("B19").Select
ActiveSheet.Paste
ActiveSheet.Shapes("Chart 2").Name = "Chart 3"
Selection.Name = "Chart 3"
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartTitle.Select
Selection.Caption = "=GraphPivot!R71C2"
ActiveChart.ChartArea.Select
ActiveChart.FullSeriesCollection(1).Values = "=GraphPivot!$C$71:$N$71"
ActiveChart.FullSeriesCollection(2).Values = "=GraphPivot!$O$71:$Z$71"
ActiveChart.FullSeriesCollection(3).Values = "=GraphPivot!$AA$71:$AL$71"
'1st repetition
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Copy
Range("B35").Select
ActiveSheet.Paste
ActiveSheet.Shapes("Chart 3").Name = "Chart 4"
Selection.Name = "Chart 4"
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.ChartTitle.Select
Selection.Caption = "=GraphPivot!R72C2"
ActiveChart.ChartArea.Select
ActiveChart.FullSeriesCollection(1).Values = "=GraphPivot!$C$72:$N$72"
ActiveChart.FullSeriesCollection(2).Values = "=GraphPivot!$O$72:$Z$72"
ActiveChart.FullSeriesCollection(3).Values = "=GraphPivot!$AA$72:$AL$72"
'2nd repetition
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.ChartArea.Copy
Range("B51").Select
ActiveSheet.Paste
ActiveSheet.Shapes("Chart 4").Name = "Chart 5"
Selection.Name = "Chart 5"
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.ChartTitle.Select
Selection.Caption = "=GraphPivot!R73C2"
ActiveChart.ChartArea.Select
ActiveChart.FullSeriesCollection(1).Values = "=GraphPivot!$C$73:$N$73"
ActiveChart.FullSeriesCollection(2).Values = "=GraphPivot!$O$73:$Z$73"
ActiveChart.FullSeriesCollection(3).Values = "=GraphPivot!$AA$73:$AL$73"
'3rd repetition
End Sub
Thanks in advance
Bookmarks