Update charts on different sheets by macro
Johan
So, I record a macro when I changed the values of the different charts on one sheet. Then I modified the macro by adding a loop over all the sheets, but when I put also that variable in the SeriesCollection (charts values are read different for all sheets!), a run-time error '1004' (Unable to set the Values property of the Series class). Can someone help me?
Dim WS_Count As Integer
Dim I As Integer
' Set WS_Count equal to the number of worksheets in the active workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 3 To (WS_Count - 1)
Sheets(I).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
With ActiveChart.SeriesCollection.NewsSeries
ActiveChart.SeriesCollection(1).XValues = "=figures!R5C6:R5C11"
ActiveChart.SeriesCollection(1).Values = "=figures!R(I+3)C6:R(I+3)C11"
ActiveChart.SeriesCollection(2).XValues = "=figures!R5C6:R5C11"
ActiveChart.SeriesCollection(2).Values = "=figures!R(I+75)C6:R(I+75)C11"
ActiveChart.SeriesCollection(3).XValues = "=figures!R5C6:R5C11"
ActiveChart.SeriesCollection(3).Values = "=figures!R(I+147)C6:R(I+147)C11"
ActiveChart.SeriesCollection(4).XValues = "=figures!R5C6:R5C11"
ActiveChart.SeriesCollection(4).Values = "=figures!R(I+291)C6:R(I+291)C11"
Windows("temp.xls").SmallScroll Down:=37
ActiveWindow.Visible = False
Windows("temp.xls").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=figures!R509C6:R509C11"
ActiveChart.SeriesCollection(1).Values = "=figures!R(I+509)C6:R(I+509)C11"
ActiveChart.SeriesCollection(2).XValues = "=figures!R509C6:R509C11"
ActiveChart.SeriesCollection(2).Values = "=figures!R(I+437)C6:R(I+437)C11"
ActiveWindow.Visible = False
Windows("temp.xls").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=figures!R509C6:R509C11"
ActiveChart.SeriesCollection(1).Values = "=figures!R(I+509)C6:R(I+509)C11"
ActiveChart.SeriesCollection(2).XValues = "=figures!R509C6:R509C11"
ActiveChart.SeriesCollection(2).Values = "=figures!R(I+582)C6:R(I+582)C11"
' The following line shows how to reference a sheet within the loop
' by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name
Next I