Dynamic Chart Source Data

Comment from sam
Time: Friday, July 4, 2008, 12:56 pm

Hi JP,

Dont use Offset – Its Volatile

Use ChrtArea = Index($1:$65536,Counta($A:$A)+1,Counta($1:$1)+1)

Assuming Data Stars from Cell A1

sam

Comment from Doug
Time: Saturday, September 27, 2008, 2:31 pm

This is a great tip. But I need help on one more step. I am setting up the dynamic update in the worksheet code. Your code works for 1 chart but I need to update 2 charts on the same page. How would I modify the VBA code to support 2 different charts on the worksheet?

Comment from Nick
Time: Friday, October 3, 2008, 4:48 pm

Jon,

Cam across your website in need of excel guidance.
This is great and I have been playing with this and learning.

However, when I execute the code, it resets the labeling of the series in the chart.
Thinking about it, I could define a name for each series and then add an update into the macro to update the series using the definition but I think there must be a quicker way of doing this especially when there are several series.

Thanks,
Nick

Comment from CIBER
Time: Wednesday, November 19, 2008, 4:21 am

I have a senario in which i have to change the source data of the graph by using some validation inputs,
Eg : i have a set of data in rows which is used to create the graph , now i have to change the source data with refference to the a particular row.
+ values to select the rows after refference row & -ve for rows before refference row .

say if i give +3 , -5 , then the source data become 5+3+1(reffernce row ) = 9 rows totally shd be used to create the graph

All this has to be done dyanamically how can it be done.

Comment from Ardalan
Time: Friday, November 6, 2009, 12:02 pm

Hi Jon,
Thanx for your great ideas.
I have a problem with making a X-Y dynamic chart. Actually i have around 30 X & 30 Y values in collumns tight together (X1 Y1, X2 Y2, etc) that can be shown in X-Y graph. What should I do that graph can be updated automatically without using source data/addseries one by one.

Comment from Ardalan
Time: Saturday, November 7, 2009, 12:50 am

Jon,
The ranges are like this:
X 1 = A1:A500 Y 1 = B1:B500
X 2 = C1:C500 Y 2 = D1:D500
.
.
X 30 = AD1:AD500 Y 30 = AE1:AE500
These data come from some calculations of other data. I have put a formula if there is no data it replies #N/A for my graph. For each X & Y graph should draw a line. I want this adding happens automatically, ie.; if I have 3 set of XY data , the chart only draw 3 lines & in case I add more to these 3, say 2, Graph should automatically add another 2 series & shows 5 lines and in case I delete one, graph reduces one.

Comment from Sam R
Time: Thursday, January 14, 2010, 4:48 am

Hi Jon,

Thanks for this tip. I am working with graphs on one excel sheet that will have to update according to data located in another sheet.

Is there anyway to modify the macro “Private Sub Worksheet_Change(ByVal Target As Range)” to take this into account?

I have tried to add sheet names to:
-Me.Range(“ChtSourceData”) –> Me.Sheets(“SheetName”).Range(“ChtSourceData”)
or
-Me.ChartObjects(1).Chart –> Me.Sheets(“SheetName”).ChartObjects(1).Chart

But got an error message every time. The reason I don’t want to simply put “=” links from one sheet to another to have all data on the same sheet is that the excel file I am working with is already quite slow due to other calculations.

Comment from Fabio
Time: Saturday, April 17, 2010, 11:32 pm

Hi Jon, I have a set of predefined charts in one xls sheet with data sources in many different sheets.

The data source sheets get refreshed from a series of Access queries.

I tried the method with naming the range but we I get the refresh I lose that information.

In other words, I would like to genrate xls charts based on new data I export from an access db.

Thanks

Comment from Fabio
Time: Sunday, April 18, 2010, 2:42 pm

Actually Access made me a very nice “present”, it dynamically exports name raanges with the same name as the query and the worksheet, so with a simple vba code my dashboard gets updated automatically! This made my day and I can stop work for this weekend! I am attaching the code below, any suggestion to improve it is more than welcome (I am far from being a programmer).

Sub Auto_Open()
‘
‘ Code by fabio_italiano
‘

‘
‘Select “Charts” Sheet
Sheets(“Charts”).Select
‘Select the graph to update after renaming with Shit+click
ActiveSheet.ChartObjects(“Radar_Chart”).Activate
‘Select Chart Area
ActiveChart.ChartArea.Select
‘Open Data Source and Select the data range, which has the same name of the data sheet
‘Please note: TransferSheet function in Access exports data in a Excel Worksheets with the same query name
‘Automatically recreating a name range with the same name each time a new export occurs
ActiveChart.SetSourceData Source:=Sheets(“SAD_By_MG_MR_Emp”).Range(“SAD_By_MG_MR_Emp”) _
, PlotBy:=xlRows
ActiveWindow.Visible = False
Windows(“WFReports.xls”).Activate
‘Select the top-left cell in the Charts sheet
Range(“A1″).Select

End Sub

Comment from Fabio
Time: Monday, April 19, 2010, 5:29 pm

Thank you Jon, I tried your optimized code but I get the following error:

“Object doesn’t support this property or method”

Comment from Samantha
Time: Wednesday, June 2, 2010, 10:38 am

Dear Jon

Thank you, I’ve been searching for such a solution for several days, so this is great.

I have tried reproducing exactly your example given here, but I keep getting a compile error.

This is exactly what I have typed, have I made an obvious mistake? I tried re-typing it several times??

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range(“ChtSourceData”)) Is Nothing Then
Me.ChartObjects(“ChtSourceData”).Chart.SetSourceData _
Source:=Me.Range(“ChtSourceData”), _
PlotBy:=xlColumns
End If

End Sub

I’m using excel 2007.
Many thanks

Comment from Samantha
Time: Thursday, June 10, 2010, 11:00 am

Thanks for the advice Jon.
Turns out I had a full stop instead of a comma, always the simple pesky mistakes! Works a dream.
Thanks again!

Comment from Jacqueline
Time: Wednesday, December 8, 2010, 4:27 pm

Thank you so much for your website, Jon! I’m a VBA beginner/intermediate level, and the code you provide helps a great deal in understanding how it works.

I have a set of chart templates, already created and formatted, where the series are not in columns but in rows and the categories are in columns. They do have to be displayed this way. So far, neither the OFFSET nor INDEX formulas work in setting the initial chart range. Is there something different I need to do when charts are set up this way?

Thanks for any help.

Comment from Daniel
Time: Monday, January 31, 2011, 5:55 pm

Hi, this is very useful code, but I have a doubt, I need the graph in a separate sheet that shows only the graph, not the data, and I always get this error “Object doesn’t support this property or method” or “out of range”, can you help me please! thanks in advance!!

Comment from Daniel
Time: Tuesday, February 1, 2011, 10:35 am

Hi Jon!! Thank you very much for this!!! warm regards!

Comment from Nancy
Time: Saturday, February 5, 2011, 4:02 am

Hi, Jon.

I need to adapt the solution you provided for Daniel with a dedicated worksheet for data, to an embedded Excel workbook supporting a PowerPoint chart.

Can you provide an example of how to address this in your 2 macros?

I am using Office:mac 2011.

FYI, the data is referenced to an external source. It seems to me that this should be compatible with your auto-update solution. But if I am wrong, please set me straight.

Thanks for the support. Much.

Nancy

Comment from Joe
Time: Monday, February 7, 2011, 9:22 am

Hi Jon,

I’ve been dealing with an error around this code for days now and I cannot figure out what’s wrong!! The code below resides in the Worksheet_Change method in my ‘Charts’ worksheet where the chart ‘Chart_FacilityTrend’ also resides. The named range ‘ChartFacilityTrendValues’ references a range in my ‘Calcs’ worksheet.

The code: ChartObjects(“Chart_FacilityTrend”).Chart.SetSourceData Source:=Range(“ChartFacilityTrendValues”), PlotBy:=xlColumns

The error: “Method ‘Range’ of object ‘_Worksheet’ failed”

Any insight would be greatly appreciated! Thanks!

Comment from Erick
Time: Monday, February 7, 2011, 9:42 am

Joe,

Try specifying the worksheet for the range in the ‘source:=’ statement.

ie source:= thisworkbook.worksheets(1).range(…

That has worked for me when using embedded objects

Comment from Joe
Time: Monday, February 7, 2011, 10:38 am

Thanks, Erick! That was the trick!!

Comment from Paul Beck
Time: Tuesday, April 26, 2011, 8:47 am

Hi Jon

I’ve tried updating the code you suggest above to place a chart in a different worksheet to the data and keep getting errors. My current code is

Sub UpdateChartSourceData4()
Charts(“CostChart”).SetSourceData _
Source:=csanalysis1.xls.Worksheets(1).Range(“UnitCostColumn”), _
PlotBy:=xlColumns
End Sub

and I get error 424 object required.

Before that with just the .Range I got a compile error – Invalid or unqualified reference. It works when chart is in same sheet as data

Sorry I’m not very familiar with VB but would be grateful for any help

regards
Paul

Comment from Markus
Time: Friday, September 16, 2011, 1:23 pm

Hi,
I am new to this forum. I am struggeling with creating multiple charts from one set of data in Excel using VBA.
I am able to create my first correct charts by:
Code:

Set chtChart = Charts.Add
With chtChart
.Name = “ANA_” & Counter1
.ChartType = xlXYScatterLinesNoMarkers
.SetSourceData Source:=Range(“=’Sheet1′!$E$3:$E$24″), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Text = “=ANA_” & Counter1
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = “X-Axis”
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “Y-Axis”
End With
MsgBox “Pause”
ActiveChart.SeriesCollection(1).XValues = “=’Sheet1′!$A$4:$A$24″
‘Adding new Series

With ActiveChart.SeriesCollection.NewSeries
.Name = Sheets(“Sheet1″).Range(“F3″)
.Values = Sheets(“Sheet1″).Range(“F4:F24″)
.XValues = Sheets(“Sheet1″).Range(“‘Sheet1′!A4:A24″)
End With

Now I would like to replace the hardcoded address-ranges (i.e. “=’Sheet1′!$E$3:$E$24″) by a dynamic address. At the time my current location on Sheet1 is E3.
For other commands I was able to replace it by:
Range(Selection, Selection.Offset(lastitem-3, 0))

This causes an error message.
Any help would be highly appreciated.

Comment from yunjing
Time: Thursday, December 29, 2011, 5:06 am

Hi, Jon.

thanks for your code, very useful.

But if I try to delete the data source that the chart using. it will pop up error

called”Method ‘Range’ of object_Worksheet’ failed”.

I believe Me.Range() can find the data source.

As I had to clear the content of the worksheet frequently, could you help me revise your code to overcome this issues.

Thanks very much.

Comment from Sal Veltri
Time: Monday, January 2, 2012, 5:17 pm

Hi Jon,

I understand the code you gave for updating a chart from a dynamic source. Is there a way to move a point on a chart and have it reflect that change in the supporting data table?

Sal

Comment from yunjing
Time: Sunday, January 15, 2012, 9:08 pm

Thanks Jon for your reply,

I added “On Error Resume Next”, so it can ignore the error temporarily, and your code can carry on even after clearing the contents of the Worksheet.

But if I Move the chart in the Worksheet to a single Chartsheet, how to let the data range auto refer to the dynamic range in the previous worksheet.

Thanks.

Comment from Abhi
Time: Wednesday, January 18, 2012, 11:29 am

Hi Jon,
Am quite new to charts (and overwhelmed) hence a simple question.
I have a nicely formatted charts with 6 different series; all against date on the Xaxis.
Using setSourceData works, however clears off all existing series/formats/headers etc.

How can I just target changing the ranges ?

Thanks in advance

Comment from Abhi
Time: Monday, February 6, 2012, 5:44 pm

Jon,
Please ignore.
I was having problems creating the name, hence resorted to the SetSourceData. Needless to say was trying to over complicate.

The following worked fine for me
INDEX(Static!$D:$D,5,1):INDEX(Static!$D:$D,cRows,1)

Deployed the solution & was very much appreciated too.
Thanks !