VBA code to Update all the graph in a workbook

[Russel225]

Hi,

I have many graph in several sheets, each time when data changes, I have to update all the charts data, one by one. so I need a code which can update all the charts in that worksheet.

charts and data are on different sheets.

 

[Derek Brown]

No need for VBA code to do this - use formulas with Dynamic Charts instead.
Have a look at "Dynamic Charts" in the index at: Jon's Excel Charts and Tutorials - Index

 

[Russel225]

Hi Derek,

Sorry for late reply, yes, there is No need for <acronym title="visual basic for applications">VBA</acronym> code to do this. but I have to do this for, more than eighty graph's.
In future I will take care of this problem but for now, I need a code to do this.

 

[Derek Brown]

charts and data are on different sheets.

You are still going to have quite a bit of work to do - see if Dynamic Chart Source Data | Peltier Tech Blog | Excel Charts gives you some ideas (the link is from the 'VBA' section of the index given in the earlier post).

 

[NdNoviceHlp]

If you VBA change the data doesn't that update the chart(s)? Here's some code that removes the original chart and builds the same type with the new specified data... seems abit much when the dynamic thing is available. Maybe the code will help. ****Use this code only on a copy of your wb in case something goes wrong. Dave

Sub UpdateChart()
Dim Ctype As String, X1Value As Range, Y1Value As Range
Dim ChartRange As Range
Application.ScreenUpdating = False
With Worksheets("Sheet1") 'change to suit
    'loop charts
    For cnt = .ChartObjects.Count To 1 Step -1
    'delete chart
    .ChartObjects(cnt).Chart.ChartArea.Select
    Ctype = .ChartObjects(cnt).Chart.ChartType
    ActiveWindow.Visible = False
    .ChartObjects(cnt).Chart.Parent.Delete
    'add new chart
    'set new chart x values
    Set X1Value = Sheets("Sheet1").Cells(1, 1)
    'set new y values
    Set Y1Value = Sheets("Sheet1").Cells(5, 2)
    Set ChartRange = Sheets("Sheet1").Range(X1Value, Y1Value)
    Charts.Add.Location Where:=xlLocationAsObject, Name:="Sheet1"
    ActiveChart.ChartType = Ctype
    ActiveChart.SetSourceData Source:=ChartRange, PlotBy:=xlColumns
    Next cnt
End With
Application.ScreenUpdating = True
End Sub

ps. data in A1:B5 in this eg

 

[Russel225]

Hi NdNoviceHlp,

Thanks for your effort but, this is not what I need.
you see, I have more than 10 sheet's in each workbook, each having multiple charts, so basically it's like building from scratch.
there must be a way to update those chart by VBA code.

 

[xenou]

Charts update automatically... What exactly do you mean by updating here?
ξ

 

[Russel225]

Hi xenou

I have fixed excel template, In which every time I have to enter new data. I simply mean, when I update the data, the chart should be updated automatically.

I know excel table's has that feature also index and match can be used, but I have no control over Fixed Excel template.

Now after updating data, I have to manually go to every chart and update the data ( More than 60 chart's)

I hope it's clear why I need a code.

 

[VBAExcelerator]

Have the same problem...

VBA defined series (assigning .XValues & .Values from arrays) in XYScatter plot. Macros work perfectly. But, chart display of series does not occur.

Have to right-click on plot area and select "select data." Select Data Source dialog window pops-up. I cancel out of that and the series appear in plot area when the window disappears.

Find the code here: Excel 2013 VBA Defined Series Not Plotting - Stack Overflow

Anyone know any way to refresh the plot area of the chart? Chart.Refresh does not work.

Thanks.