PDA

View Full Version : Update Data of Charts



Edmond
01-15-2019, 01:28 PM
Hi there,

I am facing some issue when I want to automate the update of a chart.
I am updating those charts once a week, and want the last 10 weeks (explaining the offset).
I have this code, which works, but I would like to know if there is a "simpliest/more elegant" way to proceed.



wsWar.ChartObjects("Chart 4").ActivateActiveChart.SeriesCollection(1).Values = Range(Cells(35, 6).End(xlToRight).Offset(0, -9), Cells(35, 6).End(xlToRight)) ActiveChart.SeriesCollection(1).XValues = Range(cellweek, cellweek.Offset(0, -9))
ActiveChart.SeriesCollection(1).Name = "='Warehouse Charts'!$A$5"

ActiveChart.SeriesCollection(2).Values = Range(Cells(36, 6).End(xlToRight).Offset(0, -9), Cells(36, 6).End(xlToRight))
ActiveChart.SeriesCollection(2).XValues = Range(cellweek, cellweek.Offset(0, -9))
ActiveChart.SeriesCollection(2).Name = "='Warehouse Charts'!$A$6"

ActiveChart.SeriesCollection(3).Values = Range(Cells(37, 6).End(xlToRight).Offset(0, -9), Cells(37, 6).End(xlToRight))
ActiveChart.SeriesCollection(3).XValues = Range(cellweek, cellweek.Offset(0, -9))
ActiveChart.SeriesCollection(3).Name = "='Warehouse Charts'!$A$7"


Thanks in advance for your help!

mana
01-16-2019, 04:23 AM
Dim cht As Chart
Dim ser As Series

Set cht = wsWar.ChartObjects("Chart 4").Chart


For Each ser In cht.SeriesCollection
ser.XValues = Range(Split(ser.Formula, ",")(1)).Offset(, 1)
ser.Values = Range(Split(ser.Formula, ",")(2)).Offset(, 1)
Next

Edmond
01-16-2019, 09:07 AM
Than you very much Mana.
I have a run time error when I run the code:
Run-time error '1004' : Method 'Range' of object'_Global' failed
within the line: ser.XValues
I have already had this issue but I've never really understood what it meant.

mana
01-17-2019, 03:44 AM
Could you post your workbook?

Edmond
01-22-2019, 09:36 AM
Hey Mana,

Sorry for the late answer.
I am not able to provide you my workbook. Let me try to explain you what I have:

Chart is within a specific worksheet
Data are with worksheet1

the Xvalues are based on the week
Yvalue are based on multiple series.

You will find below the beginning of my code:


Sub ChartW()

Dim wsWar As Worksheet
Set wsWar = Worksheets("W Charts")
Dim cellweek As Range
Set cellweek = wsWr.Cells(2, 6).End(xlToRight)


'Chart 5 - AOG
wsWr.ChartObjects("Chart 8").Activate

ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = "Menu"
With ActiveChart.ChartTitle.Select
Selection.Left = 12
Selection.Top = 6
End With

ActiveChart.SeriesCollection(3).Values = Range(Cells(7, 6).End(xlToRight).Offset(0, -9), Cells(7, 6).End(xlToRight))
ActiveChart.SeriesCollection(3).XValues = Range(cellweek, cellweek.Offset(0, -9))
ActiveChart.SeriesCollection(3).Name = "='W Charts '!$A$7"

ActiveChart.SeriesCollection(2).Values = Range(Cells(6, 6).End(xlToRight).Offset(0, -9), Cells(6, 6).End(xlToRight))
ActiveChart.SeriesCollection(2).XValues = Range(cellweek, cellweek.Offset(0, -9))
ActiveChart.SeriesCollection(2).Name = "='W Charts'!$A$6"