In Using Pivot Table Data for a Chart with a Dual Category Axis, I showed how to make a Chart with a Dual Category Axis using a pivot table to properly arrange the source data. I generally prefer using a regular chart, because pivot charts are pretty inflexible when it comes to formatting. Unfortunately, a regular chart doesn’t know when the pivot table has been resized by a refresh. In Dynamic Chart using Pivot Table and Range Names I showed how to use dynamic ranges to allow a regular chart to update when the pivot table changes. However, that technique only accommodates a change in the number of points in a series, not the number of series in the chart. To allow for changing height and width of a pivot table, you either need to revert to a pivot chart, or use a VBA procedure, which I describe below.
This example begins with the same data range, pivot table, and chart as the previous ones.
I’ve highlighted some sections of the pivot table above to correlate them with pivot table ranges in the VBA object model.
| Color | Range |
| Blue | DataBodyRange |
| Green | ColumnRange |
| Purple | RowRange |
| Yellow | DataLabelRange |
The VBA procedure is not too complicated. It should be run whenever the pivot table has been refreshed. It can be called from a button, or from a Worksheet_Calculate event procedure. Whatever works best in a particular application. Paste this code into a regular code module:
Sub UpdateChartFromPivot()
Dim rCategories As Range
Dim rValues As Range
Dim rSeriesNames As Range
Dim pt As PivotTable
Dim cht As Chart
Dim iSeries As Long
Dim nSeries As Long
' Define the Pivot Table
'Set pt = ActiveSheet.PivotTables(1)
Set pt = ActiveSheet.PivotTables("PT_ChartSource")
' Define the Ranges
Set rValues = pt.DataBodyRange
With pt.RowRange
Set rCategories = .Offset(1).Resize(.Rows.Count - 1)
End With
Set rSeriesNames = pt.ColumnRange.Rows(2)
' Define the Chart
'Set cht = ActiveSheet.ChartObjects(1).Chart
Set cht = ActiveSheet.ChartObjects("chtPivotData").Chart
' How Many Series?
nSeries = rSeriesNames.Columns.Count
' Remove or Add Series until Chart Has Correct Number
Select Case cht.SeriesCollection.Count - nSeries
Case Is > 0
' too many: remove excess series
For iSeries = cht.SeriesCollection.Count To nSeries + 1 Step -1
cht.SeriesCollection(iSeries).Delete
Next
Case Is < 0
' too few: add sufficient series
For iSeries = cht.SeriesCollection.Count + 1 To nSeries
cht.SeriesCollection.NewSeries
Next
Case Else
' just right
End Select
' Populate Each Series
For iSeries = 1 To nSeries
With cht.SeriesCollection(iSeries)
.Name = rSeriesNames.Columns(iSeries)
.Values = rValues.Columns(iSeries)
.XValues = rCategories
.Border.LineStyle = xlNone
End With
Next
End Sub
Let’s add some rows to the pivot table source data:
Now update the pivot table:
And finally, run the VBA procedure to update the chart:
The VBA procedure can be run from the Macros dialog (press Alt+F8 or navigate the menu/ribbon), from a button you’ve placed on the sheet (see Assign a Macro to a Button or Shape and Assign a Macro to an ActiveX Control), from a menu item (see Assign a Macro to a Toolbar or Menu), or from a worksheet event procedure.
In Excel 2003 (and maybe 2002) you can use the Worksheet_PivotTableUpdate event procedure. Select the sheet with the pivot table and chart, right click on the sheet tab, and choose View Code. This opens the code module for the active worksheet. From the left dropdown in the module window, choose Worksheet, and from the right dropdown, choose PivotTableUpdate. The shell for the event procedure is entered into the module for you. Insert the procedure name as shown below, and you’re ready to go:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
UpdateChartFromPivot
End SubIf you’re using an older version of Excel, there is no PivotTableUpdate event. What I do in this case is place a formula in a cell outside the pivot table (say, M1) with a formula like:
=SUM(A1:J20)where the range A1:J20 includes the pivot table. This formula ensures that a calculation occurs when the pivot table refreshes. Then I use the Worksheet_Calculate event procedure to kick off the chart update procedure. As above, select the sheet with the pivot table and chart, right click on the sheet tab, and choose View Code. This opens the code module for the active worksheet. From the left dropdown in the module window, choose Worksheet, and from the right dropdown, choose Calculate. The shell for the event procedure is entered into the module for you. Insert the procedure name as shown below, and you’re ready to go:
Private Sub Worksheet_Calculate()
UpdateChartFromPivot
End SubFourth in a series
- Chart with a Dual Category Axis
- Using Pivot Table Data for a Chart with a Dual Category Axis
- Dynamic Chart using Pivot Table and Range Names
- Dynamic Chart using Pivot Table and VBA
Update
A follow-up post shows how to Create and Update a Chart Using Only Part of a Pivot Table’s Data.
Peder Schmedling says
Hey!
Way to go, very nice solution.
I love this blog, keep up the good work.
Jon Peltier says
Peder –
I told you it was on the way! Actually I was writing it as your previous comment came in. The nice thing about the blog is I can write about smaller parts of a problem, and post a bit at a time, like chapters in a serial novel. With the regular web site, I felt I had to write the whole novel at once, so I often just never got around to it.
Subhash Tiwari says
Hi Team,
It’s nice to see the effort you people doing to help the other person as well a new comer.
I have a query…I’m working on a data range where pivot item value need to change dynamically. Once I record the Marco. It’s given a static value..
Code is: –
With ActiveSheet.PivotTables(“PivotTable2”).PivotFields(“CLOSED_DTTM”)
.PivotItems(“6/4/2008”).Visible = False
End With
Here “6/4/2008” is a current date & every day this value gets changes.
Please help me how can I change it dynamically in the VBA code
Jon Peltier says
Subhash –
This might be as easy as using today’s data in the proper format:
.PivotItems(Format(Now, "m/d/yyyy")).Visible = False
Subhash Tiwari says
Hi Jon ,
Thank for your advice. However it’s not working :-(
Even I tried Today & Date function as well…but in all cases curser stop at this line of codes & every time I need to put the values manually :-(
Jon Peltier says
1. Note that the straight quotes in my line of code have been converted by WordPress into curly quotes.
2. Make sure that Format(Now, “m/d/yyyy”) exactly matches the pivot item label you need to hide. In the Immediate Window, type this and press Enter:
?Format(Now, “m/d/yyyy”)
Subhash Tiwari says
Thanks a lot Jon :-)..now code is working fine
Felix Marusic says
John,
I’m trying apply a dynamic chart title which will should change when the data is either filtered or sorted, but have been unable to do so using a macro or via the interface. Not sure of the VBA syntax needed to pull this off.
Jon Peltier says
Have you tried the Worksheet_Calculate and Worksheet_PivotTableUpdate event procedures? These are on the worksheet’s code module. Easiest way to get there is to right click on the worksheet tab and select View Code.
Felix Marusic says
Jon,
Thanks I’ll look into it.
Felix
Doug Glancy says
Jon, I’m doing something similar with an addin that updates a workbook on its Worksheet_PivotTableUpdate event. It works great except that some, to all, of my charts (pie charts!) keep reverting to Pivot Charts. It seems like this didn’t happen when my code was in the workbook itself instead of in an addin. Also, the workbooks are actually created from templates if that makes any difference, and the pivot tables are based on external queries to Access. I can kind of prevent it if I put a Stop in the code and select a sheet, but only kind of, and putting in code to activate sheets doesn’t help. I can’t really see a pattern to which charts revert and which don’t.
I know, from you, that you can accidentally turn a regular chart into a pivot chart by hand, so I assume this is related.
Doug Glancy says
I got it. Looked at your code in this post some more and that gave me the answer. I was using the SetSourceData method (I think I got that from the macro recorder, but can’t swear to it). When I switched to SeriesCollection.XValues and .Values my charts started behaving. Thanks for your most helpful site!
Jon Peltier says
Doug –
Glad you got it sorted out. When SetSourceData overlaps with a pivot table, the chart is converted to a pivot chart. Inconvenient, but setting the X and Y values of each series maintains the independence of the chart. I’m working on a replacement chart source data dialog which bypasses SetSourceData in favor of series-by-series definitions.
Doug Glancy says
Jon, that sounds like a good project. I’ve got a nice pivot chart class that passes back references to items in a field and the like. It’s been a lot of fun to put together. Thanks again.
NMullis says
Jon, I have a workbook with several pivot charts. The charts are all Line chart types where each of the series lines represents a retailer and each of the 8 retailers may or may not be in the resulting pivot chart. On each chart I need there to be consistency among each of the retailer colors, that is, to see Retailer A always in yellow, Retailer B always in blue, Retailer C always in green, etc. If Retailer A is not represented on a given chart then another retailer should not be yellow.
To add to the complexity, I would like a way to update all pivot charts (12) at one time. Is this possible?
Thank you,
N
Jon Peltier says
For the consistent formatting, you could run a macro like that in VBA Conditional Formatting of Charts by Series Name after refreshing the pivot tables.
To refresh all of the pivot tables:
Sub RefreshAllPivots() Dim pt As PivotTable For Each pt In ActiveSheet.PivotTables pt.RefreshTable Next End Sub :
NMullis says
That is awesome, thank you! I had to adjust for having a line chart style and then all worked perfectly.
Thanks again! N
jeff weir says
Just a thought inspired by NMullis’ formatting issue: Instead of using VBA, you could use the Getpivotdata function to pull series data outside of the pivottable into a ‘parking space’, and then use dynamic named ranges to reference that parking space, so the size of the chart series array always matches the size of the pivot table columns. That way, the formatting of your different series wouldn’t change.
ALso, if you switced the order of the fields around in the Values field (or Data field as its called in excel 2003) – or take one data field out out entirely – it doesn’t affect your graph…because the data returned to the ‘parking area’ is specified by a cannibalised GETPIVOTDATA function, which doesn’t care which row or column the data is in, as it references the pivot cache, not the pivot table.
Whereas if you were using a dynamic range with the OFFSET function that referenced a particular row where the data originally was, then suddenly you’d have the wrong data in your chart, as the data you wanted when you set up the range would have moved.
By ‘cannabalise’, I mean a stripped-back GetPivotData function like this:
=GETPIVOTDATA(“Sum of Revenues”,Sheet1!$B$2,”Client 1″,A2)
…where all other field row references have been stripped out.
You’d enter this in Cell B2 of the parking area, and copy down. Cell A2 would just point to the Field row names in the pivottable itself.
If the structure of the pivottable was likely to change (eg if you added a field to the ‘Column Labels’/’Column Fields’) then you’d have an issue, as your GETPIVOTDATA formulas would return errors. But you could probably build a contingency around this in to your GETPIVOTDATA formula using an IF statement or two.
Any thoughts?
jeff weir says
Just realised I didn’t explain something fully. Where I said:
“If the structure of the pivottable was likely to change (eg if you added a field to the ‘Column Labels’/’Column Fields’) then you’d have an issue, as your GETPIVOTDATA formulas would return errors. But you could probably build a contingency around this in to your GETPIVOTDATA formula using an IF statement or two.”
…I meant say take the case where you want to compare sales in your pivottabel in different years, and you drag a ‘Year’ field to the ‘Column Labels’/’Column Fields’. Suddenly the GETPIVOTDATA formula posted above wouldn’t work, because it doesn’t have a ‘Year’ reference in it, and Excel doesn’t know what year you are interested in.
Jon Peltier says
Jeff –
GETPIVOTDATA can certainly be used to update a worksheet range (chart data source) when a pivot table is refreshed. I always seem to get myself messed up when I use GETPIVOTDATA, but that’s probably because I don’t use it much.
The VBA approach here doesn’t need to know the number of defect categories (columns in the pivot data range and series) or defect subcategories (rows in the pivot data range and X axis categories), nor the names of these items. Note how I added the “Environmental” category, and the code needed no adjustment. It’s simple, it just divides the pivot data range into columns as long as the data range, and in fact you could modify it slightly to work on a non-pivot-table range.
GETPIVOTDATA would need to rely on named ranges that reference the pivot table, it would need some formulaic approach to extract the categories to use as its arguments, and you would need to make sure you had enough columns and rows of GETPIVOTDATA formulas to handle any added data (and these would have to be ignored by the chart if they were unfilled).
I neglected to note that I sorted both row area fields (Main Category and Sub Category) in decreasing order of Sum of Defects, so the highest bars in the chart stay to the left. I guess you could write funky formulas to get this result from the GETPIVOTDATA function, but my head doesn’t hurt as much if I use VBA.
If you want the series formats to be tied to a series name, as they can be using GETPIVOTDATA, you can always use VBA Conditional Formatting of Charts by Series Name to accomplish this. This would make sure that Hydraulic stayed blue even when Environmental was added higher in the series plot order in the last chart. This could be called at the end of the chart update procedure presented above.
Jon Peltier says
Another nice thing the VBA approach did without modification. If the Sub Category field is removed from the rows area of the pivot table, the chart updates very nicely, with a summary by main category without the second tier of category labels.
jeff weir says
I’ve just had a play with this, and found it VERY cool indeed. I’ve been using GETPIVOTDATA functions and parking spaces as per my earlier comment above. I now see the error of my ways….
Only thing is that my data has individual products in my one and only row field, and months of the year in the column field, with sum of revenue in the data field. So the layout is similar to the picture you posted in your last comment, except that every square in the matrix has data in it. i.e. something like this:
Jan Feb March
Product 1 10 12 12
Product 2 5 6 8
Product 3 15 13 2
Product 4 8 7 6
I’m trying to work out how to amend your code so that I can plot each product against time on seperate line graphs called ‘Product 1 graph’, Product 2 graph’, etc.
I suspect I just need to switch a few things in your code around, and add a loop so it updates every product chart. But I’m struggling….still learning this VBA beast, not to mention proper pt diction.
Any chance you could help steer me in the right direction? Of course, I realise it would be some miracle if you actually have ANY free time between earning a living and answering the zillions of requests you get like this :-)
Meanwhile I’ll keep chipping away to see if I can crack it. ANd crack it I must…this cuts down on SOOOO much complexity in the way I currently do things.
Jon Peltier says
Jeff –
Is it as simple as you want to plot each row as a series, with the months as your category labels, and the product names as series names? I’ve gotten into the habit of always putting my X values in the row fields, with my series in columns. I always have to stop and think when the data is arranged differently.
I have a post waiting to be written which will help describe how to program pivot tables,
Jeff Weir says
Exactly. I’ve taken your advice, and put X values in the row fields, with series in columns
I’ve had a go at modifying your code so it just plots one product (i.e. I filtered the pivottable to 1 product).
I hardcoded the macro so it just dealt with one series on the chart, and got it to plot the series just fine. Bu I couldn’t work out how to change your macro so that I got dates for x axis names. THen in playing around trying to fix that, I somehow mucked up the macro entirely.
I’m gonna park it for now, and continue with my existing spagetti of getpivotdata formulas as I’ve got to deliver this tomorrow.
Cheers
NMullis says
Jon, I have one more question. How can I apply this code to all charts in my workbook?
Sub ColorBySeriesName() Dim rPatterns As Range Dim iSeries As Long Dim rSeries As Range Dim iColorIndex As Long Dim chtTemp As ChartObject Set rPatterns = ActiveSheet.Range("BC1:BD8") For Each chtTemp In ActiveSheet.ChartObjects With chtTemp.Chart For iSeries = 1 To .SeriesCollection.Count Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name) If Not rSeries Is Nothing Then iColorIndex = rSeries.Interior.ColorIndex With .SeriesCollection(iSeries) .Border.ColorIndex = iColorIndex .Border.Weight = xlMedium .MarkerForegroundColorIndex = iColorIndex .MarkerBackgroundColorIndex = iColorIndex .MarkerStyle = xlTriangle End With End If Next End With Next End Sub
Thank you! Your assistance has been invaluable!
N
Jon Peltier says
Sub ColorBySeriesName() Dim rPatterns As Range Dim iSeries As Long Dim rSeries As Range Dim iColorIndex As Long Dim chtTemp As ChartObject Dim wsTemp As Worksheet Set rPatterns = ActiveSheet.Range("BC1:BD8") For Each wsTemp In ActiveWorkbook.Worksheets For Each chtTemp In wsTemp.ChartObjects With chtTemp.Chart For iSeries = 1 To .SeriesCollection.Count Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name) If Not rSeries Is Nothing Then iColorIndex = rSeries.Interior.ColorIndex With .SeriesCollection(iSeries) .Border.ColorIndex = iColorIndex .Border.Weight = xlMedium .MarkerForegroundColorIndex = iColorIndex .MarkerBackgroundColorIndex = iColorIndex .MarkerStyle = xlTriangle End With End If Next End With Next Next End Sub
Barb Reinhardt says
Jon,
I’d like to add the range “ChtLabel” as the XVal and “Loc_13” as the Value and have it display in the series definition. Also, when I use your method, the “name” is not listed in the series formula. It looks something like this
=SERIES(,’Sheet1′!$C$1:$AB$1,’Sheet1′!$C$14:$AB$14,13)
and I want
=SERIES(“MyName”,’Sheet1′!ChtLabel,’Sheet1′!Loc_13,13)
How would I go about doing that?
Thanks a bunch,
Barb
Jon Peltier says
Hi Barb –
You can simply select the series and edit the series formula in the formula bar. Alternatively (but less directly), you can open the source data dialog and change the definitions of the series name, X values, and Y values in the dialog.
Excel 2007 had problems at first when the series formula was edited directly, but SP1 or SP2 fixed that.
Barb Reinhardt says
I’m not sure you understood the question, but I’ve figured out most of it. I still need to define the name of a chart series programmatically. When I use something like this:
With cht.SeriesCollection(iSeries)
.Name = rSeriesNames.Columns(iSeries)
.Values = rValues.Columns(iSeries)
.XValues = rCategories
End With
There is a name tied to the series in the chart that I want, but it’s not displayed in the series formula, nor is it displayed when I edit the series. It looks like
=SERIES(,Sheet1!B1:AC1,Sheet1!B2:AC2,1) ‘Don’t have the series right in front of me.
I want something like this
=Series(Sheet1!A1,…
Thanks again,
Barb
Jon Peltier says
Barb –
Oh, you wanted to do this programmatically. I didn’t understand why you were having that problem.
Try using the address rather than the range object:
.Name = “=” & rSeriesNames.Columns(iSeries).Address( , , xlR1C1, True)
tables says
Thanks a lot Jon :-)..now code is working fine
That is awesome, thank you! I had to adjust for having a line chart style and then all worked perfectly.
Matthew Parent says
Thanks a lot Jon The now code is working great. It is amazing what you can do with tables.
Steve Wypiszynski says
Great website, great ideas!
Wondering if you or any of your followers have ran into a situation where the default pivottables styles (from the design tab) don’t always work as advertised. For example, I’m using “Pivot Style Medium 15”, but after refreshing the table, the headings always revert to black font on a black background which makes it impossible to read.
I’ve ran into various “features” in Excel 2007 that are irritating, but can usually work around them. However, I can’t seem to rectify this situation, and it doesn’t happen to all the pivot tables in my workbook. In other words, sometimes the default style works, sometimes it doesn’t. I assume I’ve tripped some setting, but can’t figure out where.
I’ve tried removing all formatting from the tab, no fill, setting font color to automatic, and reapplying the style, and same thing occurs. In short, where the font is white, the font becomes black.
Thoughts?
Jon Peltier says
I’ve never liked the pivot table styles in 2003. There are only so many, half of them are ugly, they are not readily customizable, and they do not reapply consistently. In 2007 there seem to be some formatting options, for pivot tables and worksheet tales, but after some initialpoking around, I have stopped trying to customize them.
Samantha says
Dear Jon
Super example once again :-)
I want my chart to update in the way you describe at the very end of this article by using SUM calculation in the worksheet. (Since my values are linked and update from another sheet)
If I want my chart to update from a named range (rather than a pivot table) how do I change the syntax below:
Private Sub Worksheet_Calculate()
UpdateChartFromPivot
End Sub
I have tried various things but have not got it right yet. Please excuse my novice VBA knowledge.
Many thanks
Samantha
Jon Peltier says
The Worksheet_Calculate part is okay, but UpdateChartFromPivot makes use of the known structure of a pivot table (.RowRange, .ColumnRange, .DataBodyRange). The program would have to be adjusted to pick out the parts of the named range.
Hans says
Hey John:
Is it possible to create a xL3DColumn pivot chart using VBA from data on an xL sheet? I actually want to use Microsoft Access to programmatically export data to an excel sheet and automatically create the specified chart in Excel. The export part of the code is done. The pivot table upon which the chart will be based will have one column field (“Test Site #”) and one row field (“Recording Period”). These two fields will make up two axes (X and Y) of the 3D column chart. The third field in the excel data (“Sum of costs”) would be in the data portion of the pivot table and appear on the “Z” axis of the chart. Hopefully this makes sense. I’m a newb at working with pivot tables.
Jon Peltier says
Hans –
Don’t squander your time with a 3D column chart. If you want people to understand the data, stick to 2D. If you’re using a column chart there’s no concern about numerical axes on the floor of the chart, so it’s easier to use a clustered column, with one series per X value and one cluster per Y value, with the heights showing the Z values.
Starting with a flat table of data, you need to create the pivot table first from the data. Turn on the macro recorder while you do this manually, to get the code you’ll need. Then select a cell in the pivot table and insert a chart while still recording.
Kabir says
Below is my macro but it gives error at 5 lines
Starting Active work sheet
Please suggest
Avis says
Hi, need help with my VBA code. I have a table of informations which i have converted in a pivot table using VBA. Using the pivot table info, i have created a chart, but the problem is i need to refresh the “Workarea” information from excel drop down list rather than getting back to the pivot table and refreshing them. Could you please help,
Pivottable does not accept dynamic value.
heres my code.
Jon Peltier says
Kabir – This is a bit off-topic for the article above. Which line gives you the error?
Jon Peltier says
Avis –
I don’t get much of your problem. What’s the “Workarea”? How is the “excel drop down list” implemented? How are you trying to include a dynamic value?
avis says
Ok, maybe i miss out a lot bit in explaning my problem. I have table just like your in the example above… with pivotfields “workarea”, “week”, “Ontime”, and etc
With the info i created a pivote table like this
Workarea : BKFN-6
Count of Nb PMR On Time Nb PMR On Time
Week Num Early Late On Time Grand Total
2 100.00% 0.00% 0.00% 100.00%
3 66.67% 0.00% 33.33% 100.00%
4 60.00% 0.00% 40.00% 100.00%
5 66.67% 0.00% 33.33% 100.00%
7 75.00% 0.00% 25.00% 100.00%
8 100.00% 0.00% 0.00% 100.00%
9 0.00% 42.86% 57.14% 100.00%
Grand Total 48.82% 3.94% 47.24% 100.00%
and than a graph, but the problem is the workarea with is listed above have 5 different areas, like DIFF, SERV, IMPL … etc with each area having different values.
So when ever the different area is choosen the values change and so does the chart.
but the chart is in a different sheet and i am not willing to show the pivot table.
So i was thinkin if it possible to have drop down list in the sheet where the chart resides which can control the pivot field workarea n to be in a VBA.
This is my problem… hope you could help.
Jon Peltier says
Check out Debra Dalgleish’s Sample Excel Spreadsheets – Excel Templates page. I think example PT0013 has what you need, and PT0016 and PT0021 are related.
avis says
Thank you very much… problems solved.
Joe says
Hello Jon,
This is great – however I´m afraid I´m having difficulties with the chart object:
You define
Later on you have
But except of .Name, non of the other methods (.Values, .XValues, …) belongs to the Chart object – thus I get an (application- or object-defined) error message when trying to run that macro.
I´m on Excel 2007 – what did I do wrong?
Thanks,
Joe
Jon Peltier says
Joe –
The properties inside the With block refer to the series object, not to the chart.
What line is highlighted when the code stops, and what is the full error message? Is there a chart on the active sheet whose chart object container is named “chtPivotData”?
Joe says
Hello Jon,
Many thanks for your response!
yes the chart is on same sheet as the pivot as well as the object name is chtPivotData.
I believe I have a problem within the Range definition, just realized that there I can see message hint “Object variable or With Block variable not set” if I move the mouse over the line
Set rCategories = .Offset(1).Resize(.Rows.Count – 1)
I guess it´s related issue. However, to answer your questions:
The code stops at line
.Values = rValues.Columns(iSeries)
The entire message says “Run-time error ‘1004’: Application-defined or object-defined error.”
I get that same message also in following line:
.XValues = rCategories
Many thanks,
Joe
Jon Peltier says
Check the range definitions. Step through this part of the code, and after every line, type the following command into the Immediate Window and press Enter.
If any of these returns “True”, the corresponding object has not been correctly defined.
Joe says
Hello Jon,
Many thanks!
I followed the steps you described but still didn´t find any root cause. Any value returned ‘False’ for each of the steps.
Thanks, Joe
Jon Peltier says
Joe –
What kind of chart is it? Is the series visible before you run the code? If it’s a line or XY chart, change it to a column chart, then try the code again. If that fixes it, I’ll write a few lines to handle this problem.
Joe says
It´s a bar chart. Not sure what you mean with series visibility, the data are shown in the pivot table before I run the code.
Best Regards,
Joe
Jon Peltier says
Joe –
I had made a small error in the code that determines the number of series in the chart. I changed this
to this
This might have caused there to be one less series in the chart than expected
Joe says
Hello Jon,
I have to apolgize, I completely restarted it from scratch and now it runs without any problems – so the little issue you found was not the root cause, either.
The problem was that I tried to incorportate it into a an existing file which also had some macros already. Strange that there was no obvious interaction with existing macros or other content, since I just started now with your code and the relevant data as well as adding all the other sheets and content I had before. So same file but built new – anyway, result is fine and that´s the matter!
Many thanks for your help!!!!
Best Regards,
Joe
Joe says
Hello Again,
Actually it appears that as soon as I try to manage multiple pivots and charts in one file I run into same error as described above.
(The code stops at line
.Values = rValues.Columns(iSeries)
The entire message says “Run-time error ‘1004′: Application-defined or object-defined error.”
I get that same message also in following line:
.XValues = rCategories)
If I start from scratch for any new pivot table / chart it works.
Do you probably know any secret to reset (Values and XValues), so I could manage multiple pivot chart updates in one file?
Best Regards,
Joe
Joe says
Hold-on! Sorry forget my last blog entry, please.
It works now. There was an issue in my coding.
thank you so much and sorry for any confusion!
Joe
Dave Cherrington says
I have been struggling with Pivot Charts for a couple of days now. I followed your instructions and this was really helpful. Thanks so much!
Joe says
So I am pretty confused about how to get this code to work on a chart that is on a Chart Sheet instead of embedded within the same page. I was able to use Sheets.Select to select the pivot table sheet and then also use the same to select the Chart sheet. The issue I am running into is that when the code hits
‘ Define the Chart
‘Set cht = ActiveSheet.ChartObjects(1).Chart
Set cht = ActiveSheet.ChartObjects(“chtPivotData”).Chart
I get error 1004
Unable to get the ChartObjects property of the Worksheet class
Thanks in advance for the insight. Also, if it is related to having a Chart Sheet instead of an embedded chart, please let me know where you might see me having future issues.
Jon Peltier says
Joe –
If your chart sheet is the activesheet, use this:
Set cht = ActiveChart
Joe says
That worked great Jon! I am pretty new to Access but your tutorial was a very well done walkthrough and got my chart up and running.
The only other fine tuning point I am sticking on is how to get the resultant chart to disregard the Grand Total row name and data (see below). I was able to cause the chart not to display the Y axis name “Grand Total” but the data portion still displays.
Set rCategories = .Offset(1).Resize(.Rows.Count – 1)
TO
Set rCategories = .Offset(1).Resize(.Rows.Count – 2)
Thanks again.
Kim says
Hi Jon,
Do you have any idea how can I add “total” to my PivotChart. Thanks.
Sobuj says
Hi
your technique is a very brilliant one! However I want to modify this code to plot only one field in my chart say “Environmental” field not all fields like “Electrical”, “Mechanical”, and “Hydraulic”. Can you please assist me to accomplish this?
Thanks in advance
Jon Peltier says
Sobuj –
Glad you asked. This was a nice little topic for a new blog post. Check out Create and Update a Chart Using Only Part of a Pivot Table’s Data.
Sobuj says
Thank you very much Jon.Hats off to you!
Tony says
Hi Jon this has been a brilliant item for me, as I was having some serious head-banging moments with this one.
I’ve got an Excel 2013 workbook with a pivot table, and pivot chart in it.
The PivotTable it called PivotTable9, the chart is called “Chart 3” (with the space).
I’ve tried to call your code, but am getting an error message at this point:
Case Is cht.SeriesCollection.NewSeries
Next
The error is:
Run-time error ‘1004’:
application-defined or object-defined error.
I’m new to VBA, so am struggling to find out what the issue is.
Thanks in advance,
Tony
Jon Peltier says
Tony –
This tutorial has a regular chart made from the pivot data, not a pivot chart. As I said, sometimes I prefer using a regular chart. The code is needed because a regular chart can’t keep up with a pivot table’s changing dimensions.
You can’t add a new series to a pivot chart, because the pivot chart already has all the data from the pivot table. If the pivot chart is okay with you, it will keep up with the pivot table without needing this VBA routine.
Charles DiGiovanna says
No matter what I try the lines
.Values = rValues.Columns(iSeries)
.XValues = rCategories
Give me a Run-time error 1004 Application-defined or object-defined error
I’ve changed the names of the columns with no change in the error..
Can you help me figure out why?
Jon Peltier says
Charles –
What do you mean by “the names of the columns”?
I suspect you get that error because rValues and rCategories are not properly defined.