Multiplying graphs
May 3, 2010
In the previous post I presented a worksheet that repeated the same set of three graphs across over a hundred rows. Creating these graphs can be achieved by copying and pasting the initial row, and then pasting in the correct data. This task takes about a minute or two. However, the resulting graphs all reference data in the first row. Manually correcting adjusting the source data for over 300 graphs would be tedious and time-consuming, making it a prime candidate for automation via VBA. In this case the following did the trick:
Sub multiply_graphs()
With Worksheets("viz")
r = 9
For n = 3 To 424 Step 3
.ChartObjects("Chart " & n).Activate
ActiveChart.SeriesCollection(1).Values = "=viz!H" & r
r = r + 1
Next n
r = 9
For n = 4 To 425 Step 3
.ChartObjects("Chart " & n).Activate
ActiveChart.SeriesCollection(1).Values = "=viz!J" & r
r = r + 1
Next n
r = 9
For n = 5 To 426 Step 3
.ChartObjects("Chart " & n).Activate
ActiveChart.SeriesCollection(1).Values = "=viz!M" & r & ":X" & r
ActiveChart.SeriesCollection(2).Values = "=viz!Y" & r & ":AJ" & r
r = r + 1
Next n
End With
End Sub
Advertisement
Trackbacks