Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
676to680
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
676to680
676to680
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Update charts on different sheets by macro

Update charts on different sheets by macro
Johan
Charts has to be updated on different sheets.
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

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Update charts on .... Doppel oT
03.10.2005 13:18:35
Hajo_Zi
AW: Update charts on .... Doppel oT
03.10.2005 18:24:23
Johan
Sorry, es hatte mir nicht echt weiter geholfen.
Kannst du bitte etwa näher erklären

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige