Dynamic Chart Using Defined Name for Source Data
A dynamic chart can be constructed using dynamic ranges that change in size as data is added or removed. This technique is very powerful: you can define flexible ranges limited only by your ability to write an appropriate Refers To formula.
One limitation of this approach is that it can only account for dynamic numbers of points in a series, but not dynamic numbers of series in a chart. Using VBA you can work around this limitation.
Let’s start with a simple data range as shown below. Following best practices, the data is arranged with series in columns, the category labels (X values) are in the first column, the series names are in the first row, and the top left cell of the range is blank. Using the Define Names dialog, we can define a name using these parameters:
Name: ChtSourceData
Refers To: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)+1,COUNTA(Sheet1!$2:$2)+1)
When we click Add and then click in the Refers To box, the range defined by the Refers To formula is highlighted by the marching ants border.
When creating a chart, or editing its source data, we can enter a dynamic range name in the Data Range box.
Excel accepts this definition of the chart data, but it converts the dynamic range name to a static range address. This means that our chart does not update as the dynamic range changes in size.
Here is our chart created with the dynamic range ChtSourceData. When the plot area or chart area of the chart is selected, the source data is highlighted.
Add some data to enlarge the dynamic range. Open the Define Names dialog, select ChtSourceData, and click in the Refers To box: The highlighted range shows that the dynamic range has enlarged to include all the added data.
The chart has not changed, but with a simple VBA procedure we can change the chart’s source data range to reflect the new dimensions of our defined range:
Sub UpdateChartSourceData()
With ActiveSheet
.ChartObjects(1).Chart.SetSourceData _
Source:=.Range("ChtSourceData"), _
PlotBy:=xlColumns
End With
End Sub
This procedure should be placed into a regular code module, as described in How To Use Someone Else’s Macro.
Here is the updated chart:
To make this work more dynamically, you can include the SetSourceData command in a Worksheet_Change event. Right click on the sheet tab, choose View Code, and the VB Editor will appear with a code module representing the active sheet. Put this procedure into the module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("ChtSourceData")) Is Nothing Then
Me.ChartObjects(1).Chart.SetSourceData _
Source:=Me.Range("ChtSourceData"), _
PlotBy:=xlColumns
End If
End Sub
This procedure executes whenever there is a change to a cell on the worksheet (not a calculation, but a change in an entered value or formula). The If checks whether the change occurred within the dynamic range ChtSourceData; if so, it resets the chart’s source data range. It’s automagic.
Better Way: Dynamic Chart Using Table for Source Data
There’s an easier way to make your whole chart dynamic, adding or removing points or series in the chart as rows and columns are removed or added in the source data range. This approach uses an Excel Table; it does not require brain bending Name definitions, and it doesn’t require any VBA coding. All it requires is that the chart uses a simple contiguous rectangular range.
The first step is to select the data range, and press Ctrl+T. A dialog pops up: make sure the indicated range is correct, and the Excel guesses correctly about headers, and click OK.
The range is formatted as a Table, and has the tell-tale filter dropdown buttons in the top row.
Since the chart uses the entire table for its data, meaning X and Y values use all columns of the table, series names use the header row of the table, and the series points use all rows, then if the size of the table changes by adding or removing rows or columns, the chart will continue to use the entire table, and will add or remove series and points to the chart.
This is just one example of the power and flexibility of Excel’s Tables.
sam says
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
Jon Peltier says
Sam – Thanks for the suggestion. Actually your formula links to the bottom right of the range, so this is the appropriate form of the Refers To formula:
=$A$1:INDEX($1:$65536,1+COUNTA($A:$A),1+COUNTA($1:$1))
or, since Excel inserts the sheet name:
=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,1+COUNTA(Sheet1!$A:$A),1+COUNTA(Sheet1!$1:$1))
It’s a good point about volatility of functions. Since I usually have small worksheets, I usually don’t worry about the calculation impact of my range definitions.
Doug says
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?
Jon Peltier says
Doug –
You need to define a range for each chart, and you should name each chart. To name a chart, hold Shift while selecting the cart, and type a distinctive name in the Name Box. Something like DynoChart1 and DynoChart2. If you have questions about this, see my tutorial Chart Names. Use ranges named DynoRange1 and DynoRange2. Then in the code:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("DynoRange1")) Is Nothing Then Me.ChartObjects("DynoChart1").Chart.SetSourceData _ Source:=Me.Range("DynoRange1"), _ PlotBy:=xlColumns ElseIf Not Intersect(Target, Me.Range("DynoRange2")) Is Nothing Then Me.ChartObjects("DynoChart2").Chart.SetSourceData _ Source:=Me.Range("DynoRange2"), _ PlotBy:=xlColumns End If End SubNick says
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
Jon Peltier says
Nick –
In my example, the series names are in the first row of the range “ChtSourceData”. Keep the top left cell blank, put the series names in the rest of the top row of this range, and the X values or category labels in the rest of the first row.
CIBER says
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.
Jon Peltier says
CIBER –
I generally use Forms menu controls for this, but you can use validation just as well.
When using worksheet formulas, usually OFFSET and INDEX are used to define the position and size of a range. In VBA one uses .Offset and .Resize for the same purpose. Your arguments for these functions have to include references to the cells with the validation or control link values.
For example, referring to the Name defined at the top of this article:
If cell D1 contains the value that indicates my row offset for the start of the data, I would insert it into this formula:
Name: ChtSourceData Refers To: =OFFSET(Sheet1!$B$2,Sheet1!$D$1,0,COUNTA(Sheet1!$B:$B)+1,COUNTA(Sheet1!$2:$2)+1)Ardalan says
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.
Jon Peltier says
Ardelan –
How is the source data changing? Is the range changing size?
You might be able to use the utility in How to Edit Series Formulas to adjust the last row, especially if all data ends at the same row.
Ardalan says
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.
Jon Peltier says
Ardalan –
I see. You may have one to 30 series, and you want the existing data sets to automatically be charted. Any data sets which are removed, you want the series also removed. This could ba accomplished through a dedicated VBA procedure.
I assumed one worksheet, ChartData, which had potentially plottable data in A1:P20, and which also had one embedded chart, the one to be upddated. This procedure clears all series from the chart, checks each pair of columns in the data range, and if both columns contain plottable data (e.g., not errors), a series is added using these two columns.
Sub DynamicChartRange() Const sADDRESS As String = "A1:P20" Dim rTotal As Range Dim cDynamic As Chart Dim iSrs As Long Set rTotal = Worksheets("ChartData").Range(sADDRESS) Set cDynamic = Worksheets("ChartData").ChartObjects(1).Chart ' clear the chart Do While cDynamic.SeriesCollection.Count > 0 cDynamic.Legend.LegendEntries(1).LegendKey.Delete Loop ' add series from total range For iSrs = 1 To rTotal.Columns.Count / 2 ' check columns for data If WorksheetFunction.Count(rTotal.Columns(iSrs * 2 - 1)) > 0 And _ WorksheetFunction.Count(rTotal.Columns(iSrs * 2)) > 0 Then ' we have data to plot With cDynamic.SeriesCollection.NewSeries .Values = rTotal.Columns(iSrs * 2) .XValues = rTotal.Columns(iSrs * 2 - 1) End With End If Next End Sub
Sam R says
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.
Jon Peltier says
“Me” refers to the sheet that the code is attached to. Since the code fires when the data changes, it should go on the code module for the sheet with the chart data.
Me.Range(“ChtSourceData”) is still correct. To access the chart you need
ThisWorkbook.Sheets(“SheetName”).ChartObjects(1).Chart
Note that the Worksheet_Change event will not detect a change if the formulas update to new values but the formulas themselves are unchanged. You can use the Worksheet_Calculate event, but you have to be careful that this doesn’t really bog things down.
Link formulas shouldn’t bog down the workbook very much.
Fabio says
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
Jon Peltier says
Fabio –
Are you defining names using dynamic formulas? Does refreshing the queries delete cells, or merely clear unused cells?
Fabio says
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
Jon Peltier says
Fabio says
Thank you Jon, I tried your optimized code but I get the following error:
“Object doesn’t support this property or method”
Jon Peltier says
Fabio –
My bad, I was trying to do it too quickly. I left out one little keyword. I’ve corrected my code.
Samantha says
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
Jon Peltier says
Samantha –
Make sure the code is in the worksheet’s code module: right click on the sheet tab and choose View Code to ensure that the correct code module is active.
Also make sure the double quotes surrounding the name of the range are normal straight quotes, not curly quotes.
Samantha says
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!
Jacqueline says
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.
Jon Peltier says
Jacquelne –
In the code you have to change
PlotBy:=xlColumns
to
PlotBy:=xlRows
The Offset should work the same way, but the formula as stated assumes the top left cell of the data range is blank and that there’s no other data in the sheet that may get counted.
Daniel says
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!!
Jon Peltier says
Daniel –
A chart sheet has a different hierarchy in the object model. Change the first block of code to:
and change the second to:
Daniel says
Hi Jon!! Thank you very much for this!!! warm regards!
Nancy says
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
Jon Peltier says
Nancy –
Two potential issues:
1. Mac Excel. Even the versions of Mac Office that contain VBA have an ancient underpowered version, equivalent to the VBA in Excel 97 for Windows. I don’t know what works and doesn’t work.
2. Code and links in embedded workbooks do not always seem to work as expected, so I almost never work with embedded workbooks. My preference is to keep the Excel parts in a standalone Excel workbook, copy the chart, and paste-link it into PowerPoint.
The fact that the source data is linked to an external source should have nothing to do with how these routines work. However, if you are relying on the Worksheet_Change event, it will not fire on an update from an external source. Worksheet_Calculate or even Workbook_Open (in the ThisWorkbook module) will be more likely to update reliably. I don’t know whether the embedded workbook has to be activated for any of this to take place.
Joe says
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!
Erick says
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
Joe says
Thanks, Erick! That was the trick!!
Paul Beck says
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
Jon Peltier says
Try
Source:=Workbooks(“csanalysis1.xls”).Worksheets(1).Range(“UnitCostColumn”)
Markus says
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.
Jon Peltier says
With respect to E3 (the selection), E3:E24 can be defined as
Selection.Resize(22)
Play with Excel and VBA. Tile the Excel and VB Editor windows side by side, and open the Immediate Window in the VB Editor. Type queries like
? Selection.Address
in the Immediate Window, and it will tell you the address.
Type commands like
Selection.Offset(5).Select
Selection.Resize(3,5).Select
in the Immediate Window, and watch how the selection changes.
yunjing says
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.
Jon Peltier says
Yunjing –
Don’t delete the rows or columns containing the data. This deletes the home cell of the named range (B2 in my example) so that the names range is undefined:
=OFFSET(Sheet1!#REF!,0,0,COUNTA(Sheet1!#REF!)+1,COUNTA(Sheet1!$2:$2)+1)
Simply clear the range (select the range and press Delete or Clear Contents).
Sal Veltri says
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
Jon Peltier says
Sal –
This used to be possible in Excel 2003 and earlier. You probably know this, and you probably were disappointed when Microsoft removed this capability.
I’m working on a way to accomplish this, but it’s not yet fully developed. I may write about it sometime in the future.
yunjing says
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.
Abhi says
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
Jon Peltier says
Abhi –
SetSourceData should not be changing any formatting. What else is in your code?
Abhi says
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 !
marc says
Hi Jon,
Hope you are doing great. Having issues with using named ranges formulas in excel 2007 charts. for whatever reason when ever i try inserting the named range in the chart i receive a error message around the formula, allthough the named range formula has no error in it, it extracts the data i need.
i have 15 data points in the area d5:r5, and i want to chart last 12,last 10, last 6, last 5 data points.
This is the formula i use for last 12 data points
=INDEX(Sheet1!$D5:$AC5,COUNT(Sheet1!$D5:$AC5)-11):INDEX(Sheet1!$D5:$AC5,COUNT(Sheet1!$D5:$AC5))
Can you please help, i can send you my file if its of any use
Thanks a lot,
Marc
Jon Peltier says
Marc –
Make sure you’re up to date with Office 2007 Service Packs.
Make sure none of the ranges have names starting with “chart”.
Also, you cannot edit names in the formula bar in 2007, only fixed cell references. Big oops on this one. You need to go to the Edit Source Data dialog, and edit each series individually.
rajesh says
hi,
I am having power point with many graphs in each slides.data of each graph is different and is from excel data.(not an OLE ).excel data for each graph comes from another excel.So when that excel gets updated ,vba should update powerpoint.I donno how to code the data soure of a graph in a slide using VBA in powerpoint.
kindly help me its urgent…………………..
Thomas says
Hi Jon,
Whenever i run the code that you gave above
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range(“chtRange”)) Is Nothing Then
Me.ChartObjects(1).Chart.SetSourceData _
Source:=Me.Range(“chtRange”), _
PlotBy:=xlColumns
End If
End Sub
It always ends with an error “Method ‘Range’ of object ‘_Worksheet’ failed
My graph and data are on the same worksheet called Calculations and the name given for my range is chtRange
Jon Peltier says
Thomas –
Which line is highlighted with the error?
Does the code run by itself, or do you have to manually run it?
Did you put the code in the worksheet’s code module or in a regular module? If you right click on the sheet tab and choose View Code, you should see this procedure.
Victoria Garcia says
Hi Jon
I have a question: I have a spreadsheet with about 30 graphs that I have to update monthly with the next month’s data. I don’t want to change the range on each graph individually (for each of the 30 graphs) – is there a way to have a control date range that I would only change in one place and all the graphs would be updated with this new range?
This would save me a lot of time.
Thank you,
Victoria
Thomas says
Hi Jon,
The sentence “If Not Intersect(Target, Me.Range(“chtRange”)) Is Nothing Then” is highlighted.
The error appears whenever i try to change something on the excel worksheet. Regardless of whether it is inside the chtRange that i defined. “=INDEX(Calculations!$J$1,COUNTA(Calculations!$J:$J)+1,COUNTA(Calculations!$J23:$M23))”
I followed your instructions to right click on the sheet tab and view code and then paste my code onto the open window in VBA
Jon Peltier says
Thomas –
If you use INDEX, the first argument must contain all possible places you’d be indexing. If either of your COUNTAs are not exactly 1, then you will get an error (#REF!). And even if you expand the first argument to (say) Calculations!$J$1:$M$23, INDEX the way you’re using it will only identify a single cell as your target range.
This would identify a 2D range:
=Calculations!$J$1:INDEX(Calculations!$J$1:$M$23,COUNTA(Calculations!$J:$J)+1,COUNTA(Calculations!$J23:$M23))
Jon Peltier says
Victoria –
You’d need to set up 30 separate clauses in an If-Then or Select Case block. Yikes.
Or you could see if you could use the little tool I described in How to Edit Series Formulas.
Marianne says
Hi Jon,
How would it work if I have following Source Data and I would want to add rows only?
=’1024′!$F$1;’1024′!$F$24:$F$66;’1024′!$J$1;’1024′!$J$24:$J$66
Niel Trivedi says
Jon,
I followed your site to automate the min and max on Y axis. However, the min and max values could change depending on multiple calculation events. So I have a formula in Cell C93 and C94 to pull the min and max using simple excel built functions. The problem lies in the fact that since the values in these cells are formula driven I understand the Target.Value is not the right solution. So I went on a goose chase to find an alternative and now I am back to you. All I want to do is use your code to auto update the min / max scales on the y – axis based on the values in C93 and C94 and that these values are formula driven. By the way, your code works flawlessly if I manually input values in this cell.
As you can see I tried to go out on the web and found some ifs intersect but nothing worked.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim KeyRange As Range
With Range(“C93:C94”)
Set KeyRange = .Cells
On Error Resume Next
Set KeyRange = Application.Union(.Cells, .Precedents)
On Error GoTo 0
End With
If Not Application.Intersect(Target, KeyRange) Is Nothing Then
Select Case Target.Address
Case “$B$93”
ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case “$B$94”
ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case “$B$95”
ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlCategory) _
.MajorUnit = Target.Value
Case “$C$93”
ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlValue) _
.MaximumScale = Target.Value
Case “$C$94”
ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlValue) _
.MinimumScale = Target.Value
Case “$C$95”
ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlValue) _
.MajorUnit = Target.Value
Case Else
End Select
End If
End Sub
‘Read more: Link Chart Axis Scale Parameters to Values in Cells https://peltiertech.com/link-excel-chart-axis-scale-to-values-in-cells/
Your help will be much appreciated.
Jon Peltier says
Niel –
In this case, you need to use the Worksheet_Calculate event.
Niel says
Jon,
Thanks for getting back to me quickly. The only problem I think I am having is
1. When I Open and Close or work on another Excel file I get debug errors. I thought the code was private worksheet and is inserted specifically on the sheet where the chart is. So I don’t understand why working on another file or tab gives me a debug error.
2. I have a similar chart in sheet 2 and which changes based on changes in sheet 1. So i guess i cannot use activesheet in this case the second sheet is not active while i am looking at the first.
Jon Peltier says
Niel –
1. What are the error messages?
2. This procedure goes in the worksheet with the data. Use the keyword “Me” for the sheet containing the VBA code. You need to specify the chart:
Mark Sheridan says
Hi Jon
I get a Subscript Out Of Range error when I use this code in my DPD worksheet:
Sub UpdateChartSourceData4()
Charts(“Chart 10”).SetSourceData _
Source:=Workbooks(“1205 WorkQueue Pivots May-12.xlsm”).Worksheets(“DPD”).Range(“ChtSourceData”), _
PlotBy:=xlColumns
End Sub
ChtSourceData =OFFSET(DPD!$M$4,0,0,COUNTA(DPD!$M:$M)+1,COUNTA(DPD!$2:$2)+6)
Can you help?
Thanks
Mark
Jon Peltier says
Mark –
That means you’re trying to reference an item in a collection that doesn’t exist. Do you have a chart named (“Chart 10”) on the active sheet? Is there a workbook named “1205 WorkQueue Pivots May-12.xlsm”, which has a sheet named “DPD”, and which includes a range named “ChtSourceData”?
Niel says
Thank you! Thank you. Works like a charm.
Mark Sheridan says
Jon
Yes I have rechecked everything and all names are correct. I have the code in the DPD sheet. I can’t work it out. Very frustrating.
Mark
Jon Peltier says
Mark –
Too early to give up.
Which line is highlighted when you see the error?
Also, are all of the apparent spaces in the strings real spaces, and not non-breaking spaces?
Is the chart a standalone chart sheet, or is it embedded in a worksheet? Your syntax is for a standalone chart sheet. For an embedded chart it would be
instead of
Sometimes what I do is split out the pieces of the long lines, to see which piece is triggering the error:
Mark Sheridan says
Hi Jon
Yes, it was the embedded chart as opposed to chart sheet that was the problem (although the error message never highlighted any part of the code).
Amended code:
Sub UpdateChartSourceData4()
Worksheets(“DPD”).ChartObjects(“Chart 10”).Chart.SetSourceData _
Source:=Workbooks(“Copy of 1205 WorkQueue Pivots May-12.xlsm”).Worksheets(“DPD”).Range(“ChtSourceData”), _
PlotBy:=xlColumns
End Sub
It works now.
Thanks so much for your help.
Mark
peter says
Hi Jon – please give us (me and the voices in my head) a hand to create dynamic charts from data sitting in non-adjacent columns. The data is generated from a query and placed in the sheet. The chart is located on a seperate sheet.
Any help will be highly appreciated
Regards
Peter
Jon Peltier says
Peter –
This is dependent on the structure of the data and on what subset of the data you want to plot.
Anne says
Hello Jon,
Thank you for your help. I have another problem though. I want my graph to only take into account cell that have a value in them. My source looks like
Date Value
Date Value
Etc, except I prepared the file so that it would react to new lines thanks to formulas in the empty cells underneath those already filled: IF(A1=””,””,A1) so I have twenty lines or so underneath the ones with value in them (A1) that have no value but a formula in them.
Excel still includes them in the creation of the graph. Can I somehow add a condition to the code (or the source of the graph) so that only the cells with value are taken into account?
Jon Peltier says
Anne –
There are other ways to deal with expanding data ranges:
Easy Dynamic Charts Using Lists or Tables
Dynamic Charts
Harsha says
I am trying to change the data sources based on a particular selection in the drop down. Could you please suggest, how should I do it???
Sanjeev Pandey says
Hi Jon,
Thanks for your excellent VBA skills, it has helped me alot today in going through dynamic chart plotting, especially the VBA MultiXYChart: https://peltiertech.com/Excel/ChartsHowTo/VBAExcelXYCharts.html#manyxoney
However, I have an issue here, your code works well when I define a range containing 8 to 9 columns for a chart, but I don’t know how to get it create different chart for different data sets (each containing 8 to 9 columns of data) in the same sheet? For example I can now plot a chart for the range B1:Q15, but I am not able to define multiple ranges such as R1:AW15, AX1:BE15 and so on to plot a chart for each data set. Lets say I want this macro to loop through a range of datasets and plot a chart for each of them, it could be 30 to 40 charts in the same sheet. I need your help.
Thanks in advance!
Jon Peltier says
Sanjeev –
This is a tricky problem. If the sets of data are regularly arranged (always the same addresses on every sheet), you could hard code the range addresses, then loop through these. Or perhaps you could list the ranges in a worksheet range, and loop through this range, processing each address as you proceed. Or you could write complicated algorithms to hunt for suitable ranges in the sheet. Not so easy.
Sanjeev Pandey says
Jon,
Thanks for your reply. Well, eventually I found a solution, thats seems to be a dynamic one and it works. I used stepped function to call the pack of data sets. Like here:
For j = 2 To 49 Step 16
range(Cells(1, j), Cells(15, j + 15)).Select
‘Jon’s MultiXY_Chart Code
Next j
It has datapacks each of 16 columns and there would be as many as 15 to 20 datapacks. Please tell me if you could think of a problem that may occur in future, or any other amelioration would be wonderful.
Sanjeev Pandey says
Hi Jon,
This is regarding your MultipleXYChart code that plots several series in the same chart!
I need a solution for a problem which might seem simpler to you, but I’ve no idea where to go! I have plotted several charts in a excel sheet, now I want to give an individual title to each chart, and since each chart has its own particular data range, it also has a column from where it can pull text from a cell to update its title. For e.g. the first chart can pull cell value from column A, second chart can pull from column K and so on.
How would you solve this evasive problem?
Thanks alot!
Sanjeev
Jon Peltier says
Sanjeev –
Are the cells with the titles in a particular relation with respect to the plotted data? For example, if the data is in column C starting in C2, is the title in cell C1? (FYI, this is the most efficient way to arrange the data, assuming you have control over the worksheet.)
If not, you’re doing it manually, or at best, semi-manually.
Sanjeev Pandey says
The titles are present in the column before the data columns! For e.g. data is between columns C to J through 15 rows and title is in Column B (through 15 rows but same in all, so we just need to refer to one cell). How do I reference a cell in column B as chart title which has its data in columns C to J?
Jon Peltier says
Sanjeev –
First of all, if the names are adjacent to the values, the easiest way to assign them is to create the chart with the range that includes the names.
But some users don’t know that, and charts are created without series names. The code below looks at the series formulas, determines whether the data is in rows (like yours) or in columns, then finds the cell to the left (as in your case) or above the values, and inserts it into the series formula.
First there are three procedures which you would call to assign series names to (a) the selected series in the active chart, (b) all series in the active chart, or (c) all series in all charts in the active sheet. Then there is an intermediate procedure, then finally the procedure that does the work of parsing the formula and inserting the address of the cell with the name.
David says
Hi Jon
Referencing your two code snippets in the above article, how would I go about getting the chart to update ‘on open’? I know where I would place the code…but the data is being placed in the worksheet via export…so it’s essentially closed when changes to the sheet occurs.
If I simply place the following code into the Workbook_Open () code area….
If Not Intersect(Target, Worksheets(“ClaimTypeOpen”).Range(“ChtSourceData”)) Is Nothing Then
Worksheets(“ClaimTypeOpen”).ChartObjects(1).Chart.SetSourceData _
Source:=Workbooks(“InjuryIllness_Template.xlsm”).Worksheets(“ClaimTypeOpen”).Range(“ChtSourceData”), _
PlotBy:=xlColumns
End If
With Worksheets(“ClaimTypeOpen”)
.ChartObjects(1).Chart.SetSourceData _
Source:=Workbooks(“InjuryIllness_Template.xlsm”).Worksheets(“ClaimTypeOpen”).Range(“ChtSourceData”), _
PlotBy:=xlColumns
End With
….of course this does not work. I’d like the chart to update on open, in addition to updating when a change occurs on the active worksheet.
Thanks for any help
Jon Peltier says
Doesn’t this work:
David says
OMG…feel like a noob now – yes that works thanks!!
I do have other questions related to this type of dynamic chart updating – should I post them here or can you email me directly (of course any solutions can be posted by you).
Jon Peltier says
If they are related to this post, ask them here. If not, follow the guidelines in Getting Answers For Your Excel Questions, please submit your question to a dedicated Excel forum.
Ethan says
Hey Jon,
First off I want to thank you for such a great website and this really awesome bit of code. I’m a total noob so I’m sorry for such a simple question. Also I apologize if this was asked in the comment section previously, I read them all and didn’t see it (or understand a lot of them!).
My problem is that in my workbook the headers (Series A, Series B, etc…) and the first column (Cat 1, Cat 2, etc…) will be controlled by a formula. For simplicity sake lets say =if(A1=”X”,”Series A”,””). When I do this for the series it works great if all of the headers are supposed to show, but when the formula returns a blank the chart legend will still contain a colored line for that particular Series. If you need a clarification let me know!
Jon Peltier says
The data is still in the chart, so it still appears in the legend.
I suggest you label the plotted data directly, instead of using a legend. This way, if the data isn’t plotted, there’s no label.
See https://peltiertech.com/label-last-point-for-excel-2007/
Ethan says
Hey Jon,
Thanks for the super fast response and link to another really cool macro. However, this doesn’t take care of my problem. The problem I have is that all of the headers, (both CAT and Series) are formulas that will extend quite a bit. For Cat they will extend up to 1000 places and 30 places for Series. This results in a chart that looks awfully compressed. Hope this helps clarify and thanks for the help.
-Ethan
Jon Peltier says
You have the code in the workbook to change the size of the data range, but you’re not defining the data range precisely. Change this:
=OFFSET(Sheet1!$C$3,0,0,COUNTA(Sheet1!$C:$C)+1,COUNTA(Sheet1!$3:$3)+1)
to this:
=OFFSET(Sheet1!$C$3,0,0,ROWS(C:C)-COUNTIF(C:C,””)+1,COLUMNS(3:3)-COUNTIF(3:3,””)+1)
Any blank cells or formulas that return “” are subtracted from the number of cells in the row or column, and the result is the number of labels in the row or column.
Ethan says
Wow,
Thanks a million. It looked like I actually had to lock a few of the references in the equation that you sent, but other then that it works like a charm. This is the updated formula if anyone is curious.
=OFFSET(Sheet1!$C$3,0,0,ROWS($C:$C)-COUNTIF($C:$C,””)+1,COLUMNS($3:$3)-COUNTIF($3:$3,””)+1)
April says
Hi Jon,
I would like to thank you for all the tips and techniques you’ve been sharing. They are all very helpful. I have one question, though. I want to create just 1 one graph with a drop-down list, and when I select from the list, the graph also updates. The catch is, every item on the list refers to a different table. How can we do this?
Many thanks, Jon!
Mike says
Jon, thank your insights & for hosting this site. I to suggest to April that she could assign a named range to each table that she wants to use as a input to the graph. (for example TableA, TableB, TableC, etc…) Next, choose cell ($A$1) for the drop-down list to control (“cell link” within Format Control for the dropdown list.) Then, create a named range GraphData and have it reference as follows
=choose($A$1,ThisSheetName!TableA,ThisSheetName!TableB,ThisSheetName!TableC)
For your graph, in “select data”, put in
=’ThisFileName’!GraphData
That will populate your graph with whichever named data range choice coincides with the drop-down list selection.
-Mike
Mark says
Hi Jon,
This site has been very helpful to me in the last week or so, so thanks for that. I’m really struggling with something though. I’ve got two different sheets in my workbook that each have their own chart. Each of those sheets pull their own data from a separate sheet in the workbook, which is linked to an outside data source. I am trying to get my charts to update automatically, but I can’t seem to figure out how to make both do so independently. I have used your short module code on its own and the private sub worksheet change on each of the sheets containing my data, but for some reason I keep getting an error message. Advice?
April says
Hi Mike,
Thanks for your advice. Actually, my graph was quite complicated (at least, to me) because I wanted it to be dynamic and interactive at the same time (well, I’m not so sure if these are the right terms ). The ranges are inconsistent so I had to use the suggestions indicated in the previous comments, like the OFFSET and INDEX functions, to define the range. Then, to select a range for the graph, I used the ActiveX combo box, and created some codes using the IF…ELSEIF… function. It worked for me, but one thing I didn’t like was that I had 53 different ranges, so I had to define at least 53 names for the ranges, then 53 if-elseif statements on the VBA…double yikes! As of now, I’ve created only 2 if-elseif statements just to test the code and the defined ranges. I’m afraid the looooong code might slow down the execution of the commands..
I really love this forum because this is where I got all the ideas for my graph!
To Jon and everybody who contributes, thanks a lot!
April says
Hi Mark,
I think, we sort of have the same problem. My main source data is located in Sheet1, but my graphs should be in Sheet2. Since I’m not really good with the VBA codes, I just decided to create a workaround. I created a duplicate source data in the same sheet as my graphs. The dummy source data are just linked to the cells in the Sheet1, where the main source data are located. It was somewhat tedious, and it adds to the file size, but it worked for me. But if there are any other solutions to this, I would really give them a try.
Jon Peltier says
Mark –
You don’t say what the error is.
Perhaps a Worksheet_Calculate or Workbook_SheetCalculate event might work. I’ve used a dummy formula that returned an average of a column of imported data to trigger the recalculation.
Simon says
Hi
I am trying to get the code working to automate the update of the chart. The dynamic range works perfectly, but (for some reason) the vba code never updates the range.
I have multiple charts in the worksheet, so am assuming i need to add the name of the specific chart i want to auto update into the vba code. Thing is… i don’t know how! Can anyone help?
Jon Peltier says
Simon –
You mean the code doesn’t update the data range of the chart? Do you have multiple charts?
Note that the code processed
.ChartObjects(1).which is the first chart in the worksheet, not the first one created, but the lowest in the stacking order (behind the rest).
You could modify the code so it worked on the active chart:
If you know the name of the chart, you could replace the index of the ChartObject with the chart’s name:
Austin says
Hi Jon,
Thank you for this great tutorial and the many others I have used to perfect my data. I am coming from the healthcare industry, specifically in clinical research. My interest in this tutorial is partially due to the need to add/remove data from my table and auto-update charts, but I have another need. My data set is semi-large (500-1500 rows), with each row of data representing a patient and an “event” or study visit. In this table there are several columns of study test results (numeric values), disease type, number of gene copies. What I am trying to do is graph some of the test results (Y axis) with the patient’s age in months (x-axis). I need to graph the data in several different ways. One group of charts would be this data graphed by disease type, another group would be graphed by # of gene copies, etc. I find that I have to copy and paste the table many times in several sheets, because when I sort the data naturally it messes up the charts because the data was referencing a range that obviously changes when I sort.
So what I want is to be able to do all these chart from a SINGLE table, where I can sort the data, take data out, add data, all without having to manually change the ranges of the data in charts or update the changed/ extra/ removed data in all sheets of copied tables. Make sense? Very tedious and time-consuming. I like the idea of named ranges and auto-updating charts, but am struggling to figure out how to apply this to my data. Any thoughts, tips, instructions would be much appreciated, thank you!
The sample worksheet image I linked below obviously contains fake names, DOB, etc. and only a small fraction of the data and many graphs I have to create, but should give you an idea
Jon Peltier says
Austin –
Sounds like a lot of work, but best practice for making a bunch of charts is to have a separate staging area containing the data for that chart, conditioned for how the chart needs to display it.
This means putting all of your data into a Table (the capital-T data repository on which Excel bestows special formatting and behavior), and either using blocks of formulas or pivot tables to extract the particular data. Pivot tables can be sorted independently of the original data range, and you can use them to cross-tab your data.
You can make pivot charts from the pivot tables, or regular charts. Each has benefits and drawbacks. Basing defined names on the pivot table will help the chart adjust to changing pivot table sizes.
Prateek kothari says
hi jon.
I have been trying to paste data from excel to powerpoint using vba.
The problem that i am facing is random i.e sometimes it works and sometimes it doesnt and it says
“Clipboard is empty or contains data which cannot be pasted here.”
It would be of great help if you reply to this question as i am trying to solve it for the last 3 days but have no solid solution.
please reply ASAP.
Thanks,
Prateek Kothari
Jon Peltier says
Prateek –
That’s yet another off-topic question. I cannot spend time with questions that do not relate to the topic.
Follow instructions in Getting Answers For Your Excel Questions to ensure a prompt response.
Josh says
Hi Jon,
Very well done!
I really appreciate the help, and have found this very useful. I have one more challenge I’d like to add to this. In the chart I am plotting, both the X and Y data change, so instead of having all series referenced to Column B (cat 1 through 7), each one of my series takes up two columns. Therefore (as an example) the first data set in my chart would be comprised of columns B and C, then the next data set would be comprised of Columns D and E etc… If you can think of a way to plot this data dynamically, I’d greatly appreciate it.
Additional Notes: I arbitrarily added the legend name above the second column of each data set (though I could just as easily have added it above the first column if that’s easier to program for the legend). Also the number of points within each of my data sets is arbitrary, so perhaps I could simply include a large number of rows (about 10,000) to ensure all data is included in each set.
Thanks,
Josh
Jon Peltier says
Josh –
For your case, using SetSourceData will not work, because the chart source data is not a simple rectangular range with the top and left edges used for series names and X values. Instead, you will have to construct each series formula and apply it to the chart.
You can define the range ChtSourceData in the same way, but then divide the range into two column wide segments, etc. Something like this (untested):
Amir says
Hello to all,
First thanks for sharing the information and have found this very useful.
I have question regarding the “Dynamic Chart Source Data”
I use this code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range(“ChtSourceData”)) Is Nothing Then
Me.ChartObjects(1).Chart.SetSourceData _
Source:=Me.Range(“ChtSourceData”), _
PlotBy:=xlColumns
End If
End Sub
But when i Delete the information the chart update but the X axis sty as before but without information.
How do i add refresh for the information the all chart will update according to the new data?
I try to add this code”ActiveChart.Refresh ” but i got error.
Thanks
Amir
Amir says
Hello to all,
First thanks for the information and the code that present, it’s very very useful.
I have question regarding the “Dynamic Chart Source Data”
I use the first one information
Name: ChtSourceData
Refers To: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)+1,COUNTA(Sheet1!$2:$2)+1)
and the VBA code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range(“ChtSourceData”)) Is Nothing Then
Me.ChartObjects(1).Chart.SetSourceData _
Source:=Me.Range(“ChtSourceData”), _
PlotBy:=xlColumns
End If
End Sub
But when i Delete information the chart was not updat, the information from the chat was Delete but the X Axis not update.
Can you help?
Thanks
Amir
Andy says
Really helpful page you have here Jon; however, I’m having trouble making this work without run time errors.
Essentially I have to changed the plot to rows (instead of columns), as the data on Report Data sheet could have any number of rows (Data Series) in column B. I used your recommendation from your comment above dated Thursday, December 9, 2010 at 3:33 pm.
Also, I changed the code (based on your comments in the comments section dated Monday, January 31, 2011 at 6:54 pm) for when the chart is on a different sheet to the source data.
My runtime error is 1004 – Method ‘ChartObjects’ of Object’_Worksheet failed
My VBA code is:
1. Worksheet Change:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range(“ChtSourceData”)) Is Nothing Then
Me.ChartObjects(1).Chart.SetSourceData _
Source:=Me.Range(“ChtSourceData”), _
PlotBy:=xlRows
End If
End Sub
2. General – UpdateChartSourceData (Module)
Sub UpdateChartSourceData()
Charts(“Resource”).SetSourceData _
Source:=.Range(“ChtSourceData”), _
PlotBy:=xlRows
End Sub
Jon Peltier says
Andy –
The Worksheet_Change event procedure has to be on the code module for the sheet with the data. “Me” in the code refers to the sheet corresponding to this code module, so
Me.Range(“ChtSourceData”)is valid. However, the chart is on another sheet, soMe.ChartObjects(1)is not right. You need to useWorksheets("Sheet with my chart").ChartObjects(1)instead.Andy says
Ah-ha! Thanks Jon. That now works. One issue now is that as I manually enter in a new row of data (new source), it adds in the plotted area to the chart, but for some reason I now have 55 series in my legend (but only have 3 rows/series of data in the source sheet).
If I look at the name manager and click in the ‘Refers To’ formula box it highlights 55 rows in the data sheet (even though there are only 3 rows of data).
The name parameters are:
Name: ChtSourceData
Refers To: =OFFSET(‘Report Data’!$B$1,0,0,COUNTA(‘Report Data’!$B:$B)+1,COUNTA(‘Report Data’!$1:$1)+1)
Data on the Report Data sheet starts in cell B1 with dates across columns (starting in C1) and series names in rows (starting in B2).
Thanks again for your help.
Ramesh says
Hello,
I am having a similar issue as Simon (Thursday, September 12, 2013 at 2:14 am). I am trying to get the code working to automate the update of the chart. The dynamic range works perfectly, but (for some reason) the vba code never updates the range. I have multiple charts on my sheet. I tried the below two suggestions that Jon recommended but I get a run-time error ‘1004’. Any help would be greatly appreciated. Thanks.
You could modify the code so it worked on the active chart:
Sub UpdateActiveChartSourceData()
ActiveChart.SetSourceData _
Source:=ActiveSheet.Range(“ChtSourceData”), _
PlotBy:=xlColumns
End With
End Sub
If you know the name of the chart, you could replace the index of the ChartObject with the chart’s name:
Sub UpdateChartSourceData()
With ActiveSheet
.ChartObjects(“MyChartName”).Chart.SetSourceData _
Source:=.Range(“ChtSourceData”), _
PlotBy:=xlColumns
End With
End Sub
Jon Peltier says
What is the error description? Run time error 1004 could mean a lot of things.
Mans says
Hi Jon,
Thanks for this code tip, I go to your blog often to check out new things to learn. I’ve been using this code a lot and modified it. It works like a charm. Now I’m doing another change and need to run the code on a hidden chart (visible=false). From what I’ve tried SetSourceData needs an active chart, right? What would be a neat solution for this?
Regards,
Mans
Jon Peltier says
No,a chart does not need to be active, nor even be in the active sheet, to use SetSourceData:
Worksheets(“Sheet1”).ChartObjects(1).Chart.SetSoutceData Source:=SomeRange
Chris says
G’day John,
Hoping for some help here – you’re topic of discussion here has been very helpful so far.
I’ve got two sheets – Catchment Data and Catchment Report.
Catchment Data has VBA programming which inserts data from a .csv file into set out XY format ready for a chart to represent it.
Catchment Report is a page of pre-set graphs which I want to link to Catchment Data.
Example:
Using first graph on Catchment Report which is named ChartPopulation and defined range on Catchment Data called DataPopulation.
I want VBA code in a UserForm (or you could suggest alternative) to on each run of the program – relink the ChartPopulation to DataPopulation.
I’ve been playing around with both codes below to get it to work but keep getting an out of range error:
Code 1:
Worksheets(“Catchment Report”).ChartObjects(“ChartPopulation”).Chart.SetSourceData _
Source:=Sheets(“CatchmentData”).Range(“DataPopulation”), _
PlotBy:=xlRows
Code 2
ActiveSheet.ChartObjects(“ChartPopulation”).SetSourceData _
Source:=ThisWorkbook.Sheets(“Catchment Data”).Range(“DataPo[pulation”)
I need the graphs to reset each time as the Catchment Data and Graph sheets are templates which are copied each time the program runs. As the user will likely do three or four Catchment Reports in the one spreadsheet.
Appreciate your help.
Chris
Jon Peltier says
The error sounds like the worksheets aren’t found by name. When you insert sheets from a template, the sheets often get names like “Catchment Report (2)” and “CatchmentData (2)”. Maybe you could create a new workbook from the template, populate the data and the chart(s), then move these sheets into the main workbook.
A few notes:
Use
Worksheets("name")in place ofSheets("name"), because all good programmers are obsessive-compulsive.Make sure the names are spelled correctly.
In Code 2,
ChartObjects(“ChartPopulation”).SetSourceDatawill fail. It has to beChartObjects(“ChartPopulation”).Chart.SetSourceData.Also,
“DataPo[pulation”is probably wrong.Finally, note that blog comments use curly quotes, and your code needs straight quotes.
Anthony says
What can I do if I clear a column in the center of my table area? For example lets say you have Series A, Series B, Series C, and Series D. If for some reason I delete Series C my graph no longer recognizes the Series D data. Because the blank column now exists in the middle of the range the formula is cut short and doesn’t continue to on to recognize the floating D Series.
Jon Peltier says
Anthony –
Most of the Name-based range definitions will fail if the range has blanks in it. The simple formulas, using functions like OFFSET, INDEX, COUNT, and COUNTA cannot work with a discontiguous range.
I can think of ways that the VBA routine could work with a range, using a column if it contains chartable values, skipping it otherwise. But these would not be simple.
Anastasia says
Is it possible to adjust the code to update all charts across all sheets? I have 10 charts on 10 sheets, each with its own source data and different number of columns listed in that exact sheet. What I would like to accomplish to have each chart reference own data and update as more columns are added
Worksheets(“summary”).ChartObjects(“Trend”).Chart.SetSourceData _
Source:=Worksheets(“Summary”).Range(“ChtSourceData”), _
PlotBy:=xlColumns
Jon Peltier says
Anastasia –
One way to do this is to have a uniquely named Names for each sheet, say, ChtSourceData1 on Sheet1, ChtSourceData1 on Sheet2, etc. Then code like this will work:
If each chart’s defined source data range is completely isolated from other data (surrounded by empty rows and columns), you don’t even need the Names. You can simply parse the series formula of one of the series to get its Y values, and use the region of data containing these Y values. For the active chart it looks like this:
So for all charts on all sheets in the active workbook:
But there’s an easier way, which doesn’t require code, and which I didn’t really know about when I originally wrote this post so long ago. If each chart uses a simple rectangular block of data, you can make that block of data into a Table by selecting it and pressing Ctrl+T. This formats the range, as shown below.
If the chart uses the entire table for its data, meaning X and Y values use all columns of the table, series names use the header row of the table, and the series points use all rows, then if the size of the table changes by adding or removing rows or columns, the chart will continue to use the entire table, and will add or remove series and points to the chart.
Using Excel Tables is so much better, that I’ve modified this tutorial to include it.
Anastasia says
Thank you, Jon for your prompt response! The first code you wrote would work for me since my data is not set up for a table.. except each sheet is named after a product so i can’t modify the sheet name. is there a way to update based on the unique name of each sheet? thank you for your help!
For i = 1 to 10
Worksheets(“Sheet” & i).ChartObjects(1).Chart.SetSourceData _
Source:=Worksheets(“Sheet” & i).Range(“ChtSourceData” & i), _
PlotBy:=xlColumns
Next
Jon Peltier says
Anastasia –
If the names are defined with the same name (“ChtSouirceData”) and scoped to the worksheet they are on and not scoped to the workbook, you could try this:
But the point of the Tables approach is that you don’t need code, and the charts are dynamic without you having to do anything when the data changes. It’s easy to create the Tables, and if the data is set up for Names as defined here, they are also well suited for Tables.
Naga says
Hi,
I am facing issue with range object in setsorucedata in chartobject.
can some one help me
Naga says
getting error “Object variable or with block variable not set”
code:
obj_Wb.Sheets(“Chart View”).Activate
Set myrange = obj_Wb.Sheets(“Chart View”).Range(“B3:M4”)
obj_Wb.Sheets(“Chart View”).ChartObjects(“Chart 1”).Activate
ActiveChart.SetSourceData Source:=myrange
Jon Peltier says
Naga –
What line is highlighted when you see the error?
The message tells me that something is not present. Either obj_Wb is not defined (most likely), obj_Wb has no sheet names “Chart View”, or sheet “Chart View” has no chart object named “Chart 1”.
Marzuki says
Hi Jon,
I’m working on a report generator using Excel macro on MS Excel 2003. Basically, it will copy some of the worksheets containing data & charts into a new workbook & then save it as a new report.
There is a private sub in each worksheet that has been copied, which contains VBA codes for auto-renaming the chart title & auto-updating the source data range. When the worksheets are copied to a new workbook, the codes will be there as well. The codes are based on your notes & look something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
If Not Application.Intersect(Range(“AB40”), Range(Target.Address)) Is Nothing Then
i = Range(“AB40”).Value
Me.ChartObjects(“Chart 3”).Chart.ChartTitle.Characters.Text = “Mismatch Trend (” & i “)”
Me.ChartObjects(“Chart 3”).Chart.SetSourceData Source:=Me.Range(“K233″:L263” ), PlotBy:=xlColumns
End If
End Sub
The codes work perfectly on the main workbook (the generator) but then it flags an error message (run-time error 424, object required) when I run it in the generated workbook (the report) by changing the value in cell “AB40”.
I’m not able to find what is the main cause of this error. I wonder why does it works in a workbook but doesn’t work in another workbook. Is there anything that I’ve overlooked?
Really appreciate it if your could give any tips or idea how to resolve this error. I’ve spent days to look for the solution from the internet but nothing was helpful.
Thanks.
Jon Peltier says
You did not indicate which line of code was highlighted when you got the error message.
Does the range address (“K233:L263”) change when the value in AB40 changes? (Not the values, but the address itself?) If not, then you don’t need to reset the source data of the chart.
Use the well-known trick of linking a chart title to a cell to update the chart title. in an unused cell, for example, AC40, enter the formula
=”Mismatch Trend (“&AB40&”)”
Select the chart title, but don’t put it into edit mode (with the cursor in the title’s text box). Click in the formula bar, type = (the equals sign), then click on the cell with the formula you just wrote. The formula bar should say =SheetName!AC40. Press Enter.
Now the chart title stays up to date, and you can dispense with the wonky Worksheet_Change event procedure.
Marzuki says
Hi Jon,
Thanks for the response. Sorry that I forgot to highlight the line with error. The error occured at this line:
Me.ChartObjects(“Chart 3”).Chart.ChartTitle.Characters.Text = “Mismatch Trend (” & i “)”
One of the objective is to make the chart title update automatically when the integer “i” changes as the number in cell ‘AB40’ changes. By right, the source data range also should change dynamically but I wasn’t finished with the code since I’ve stucked with the 424 error (planned to work on it later).
I think it is a really good idea to link the chart tittle into a cell (which I never knew it is possible). I have tried this method & I’m glad it could work! Just wondering if we could do something like this to reset the source data range.
However, I have accidentally found a solution for this issue. Apparently, the code works perfectly after I close & re-open the report file. I also not sure what really happened & how is it possible but the 424 error won’t appear anymore! So I just added a couple lines of code to close & re-open the Workbook (report) at the end of the main macro, & it does the job.
Thanks again for your sharing & tips. Truly appreciate it. :)
João says
Hello Jon,
I’m new to Excel’s Macros and VBA and my boss asked me to do some work on this.
At the star of each year, a table is to be created it has a set number of rows, but each week I have to include an extra column to the right. This is covered in your tutorial, just copy and paste the values for each week and the graph automatically updates.
Here’s the thing, I want to do a Graph with only the last 3 columns of my table. How can I automate this task?
Thanks
Jon Peltier says
João –
No need for VBA. But you will need an intermediate range. Say my table is in B2:D9 and is named Table1. I have set up a range in H2:J9 which will always point to the last three columns. The formula in H3 is
=INDEX(Table1[@],COLUMNS(Table1[@])-2), in I3 is=INDEX(Table1[@],COLUMNS(Table1[@])-1), and in J3 is=INDEX(Table1[@],COLUMNS(Table1[@])). Note thatTable1[@]means the whole row of Table1 that is in the same row as the cell with the formula.My added range shows the last three columns of the table, which are all three columns of the table.
When I add a column, the range drops the first column of the table and picks up the fourth.
And when I add another column, the range now includes the third through fifth columns of the table.
João says
Thank you Jon.
Greatly appreaciated.