Dynamic Chart using Pivot Table and VBA

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.

Regular Dual Cat Axis Chart from Pivot Table Data

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:

Updated 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 Sub

If 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 Sub

Fourth in a series

Update

A follow-up post shows how to Create and Update a Chart Using Only Part of a Pivot Table’s Data.

 

Peltier Tech Charts for Excel

Comments

  1. Peder Schmedling says:

    Hey!
    Way to go, very nice solution.
    I love this blog, keep up the good work.

  2. 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.

  3. 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

  4. Subhash –

    This might be as easy as using today’s data in the proper format:

    .PivotItems(Format(Now, "m/d/yyyy")).Visible = False

  5. 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 :-(

  6. 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”)

  7. Subhash Tiwari says:

    Thanks a lot Jon :-)..now code is working fine

  8. 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.

  9. 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.

  10. Jon,

    Thanks I’ll look into it.

    Felix

  11. 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.

  12. 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!

  13. 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.

  14. 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.

  15. 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

  16. 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

    &nbsp:

  17. That is awesome, thank you! I had to adjust for having a line chart style and then all worked perfectly.

    Thanks again! N

  18. 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?

  19. 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.

  20. 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.

  21. 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.

  22. 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.

  23. 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,

  24. 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

  25. 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

  26. 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
     
  27. 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

  28. 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.

  29. 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

  30. 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)

  31. 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.

  32. Thanks a lot Jon The now code is working great. It is amazing what you can do with tables.

  33. 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?

  34. 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.

  35. 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

  36. 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.

  37. 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.

  38. 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.

  39. Below is my macro but it gives error at 5 lines
    Starting Active work sheet
    Please suggest

    Sub Macro4()
    '
    ' Macro4 Macro
    '
    
    '
        ActiveCell.Cells.Select
        Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Sheet1!R1C1:R1048576C29", Version:=xlPivotTableVersion14).CreatePivotTable _
            TableDestination:="Sheet19!R3C1", TableName:="PivotTable4", DefaultVersion _
            :=xlPivotTableVersion14
        Sheets("Sheet19").Select
        Cells(3, 1).Select
        With ActiveSheet.PivotTables("PivotTable4").PivotFields("Submitter")
            .Orientation = xlRowField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable4").PivotFields("Service Desk Rating")
            .Orientation = xlRowField
            .Position = 2
        End With
        With ActiveSheet.PivotTables("PivotTable4").PivotFields("Incident Id")
            .Orientation = xlRowField
            .Position = 3
        End With
        With ActiveSheet.PivotTables("PivotTable4").PivotFields("Completed Survey Date" _
            )
            .Orientation = xlRowField
            .Position = 4
        End With
        With ActiveSheet.PivotTables("PivotTable4").PivotFields("Completed Survey Date" _
            )
            .Orientation = xlPageField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
            "PivotTable4").PivotFields("Incident Id"), "Count of Incident Id", xlCount
        ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
            "PivotTable4").PivotFields("Service Desk Rating"), _
            "Count of Service Desk Rating", xlCount
        With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
            "Count of Service Desk Rating")
            .Caption = "Average of Service Desk Rating"
            .Function = xlAverage
        End With
    End Sub
  40. 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.

        Sheets("TPMC_RawData").Select
        Sheets("TPMC_RawData").Name = "TPMC_RawData"
        Cells.Select
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "TPMC_RawData!C1:C15").CreatePivotTable TableDestination:="", TableName:= _
            "PivotTable2", DefaultVersion:=xlPivotTableVersion10
        ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(130, 1)
        ActiveSheet.Cells(130, 1).Select
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Workarea")
            .Orientation = xlPageField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Week Num")
            .Orientation = xlRowField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Nb PMR On Time")
            .Orientation = xlColumnField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
            "PivotTable2").PivotFields("Nb PMR On Time"), "Count of Nb PMR On Time", _
            xlCount
        Range("B5:G38").Select
        With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
            "Count of Nb PMR On Time")
            .Calculation = xlPercentOfRow
            .NumberFormat = "0.00%"
        End With
    
    End Sub
  41. Kabir – This is a bit off-topic for the article above. Which line gives you the error?

  42. 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?

  43. 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.

  44. 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.

  45. Thank you very much… problems solved.

  46. Hello Jon,
    This is great – however I´m afraid I´m having difficulties with the chart object:
    You define

    Dim cht as chart

    Later on you have

            With cht.SeriesCollection(iSeries)
                .Name = rSeriesNames.Columns(iSeries)
                .Values = rValues.Columns(iSeries)
                .XValues = rCategories
                .Border.LineStyle = xlNone
            End With

    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

  47. 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”?

  48. 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

  49. 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.

        Set pt = ActiveSheet.PivotTables("PT_ChartSource")
        '' Immediate Window: "? pt Is Nothing"
    
        ' Define the Ranges
        Set rValues = pt.DataBodyRange
        '' Immediate Window: "? rValues Is Nothing"
    
        With pt.RowRange
            Set rCategories = .Offset(1).Resize(.Rows.Count - 1)
            '' Immediate Window: "? rCategories Is Nothing"
    
        End With
        Set rSeriesNames = pt.ColumnRange.Rows(2)
        '' Immediate Window: "? rSeriesNames Is Nothing"

    If any of these returns “True”, the corresponding object has not been correctly defined.

  50. 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

  51. 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.

  52. 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

  53. Joe –

    I had made a small error in the code that determines the number of series in the chart. I changed this

                ' too many: remove excess series
                For iSeries = nSeries To cht.SeriesCollection.Count + 1

    to this

                ' too many: remove excess series
                For iSeries = cht.SeriesCollection.Count To nSeries + 1 Step -1

    This might have caused there to be one less series in the chart than expected

  54. 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

  55. 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

  56. 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

  57. 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!

  58. 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.

  59. Joe –

    If your chart sheet is the activesheet, use this:

    Set cht = ActiveChart

  60. 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.

  61. Hi Jon,
    Do you have any idea how can I add “total” to my PivotChart. Thanks.

  62. 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

  63. 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.

  64. Thank you very much Jon.Hats off to you!

  65. 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

  66. 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.

  67. 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?

  68. 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.

Trackbacks

  1. […] quite new on vba and I try to understand how to change a chart with VBA. I found this usefull link Dynamic Chart using Pivot Table and VBA – Peltier Tech Blog. But I don't understand it completely. Let's have a look on the screenshot: […]

  2. […] – Explore Your Data With Pivot Tables – Preliminary Data Exploration with Excel Pivot Tables – Making Regular Charts from Pivot Tables – Update Regular Chart when Pivot Table Updates – Using Pivot Table Data for a Chart with a Dual Category Axis – Grouping by Date in a Pivot Table – Referencing Pivot Table Ranges in VBA – Copy a Pivot Table and Pivot Chart and Link to New Data – Pivot Table Conditional Formatting with VBA – Dynamic Chart using Pivot Table and Range Names – Dynamic Chart using Pivot Table and VBA […]

  3. […] Pkg Prc' (will always have the same number of results for both 'Pkgs and 'Avg Pkg Prc'). The info here is the closest (that I have found) to fitting my needs. (ie. Updated Pivot Chart based on the new […]

  4. […] posted several examples of manipulating pivot tables with VBA, for example, Dynamic Chart using Pivot Table and VBA and Update Regular Chart when Pivot Table Updates. These examples included specific procedures, and […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0