Comment from Peder Schmedling
Time: Saturday, April 26, 2008, 9:42 am

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

Comment from Subhash Tiwari
Time: Friday, June 6, 2008, 11:29 am

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

Comment from Subhash Tiwari
Time: Monday, June 9, 2008, 9:20 am

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

Comment from Subhash Tiwari
Time: Monday, June 9, 2008, 11:52 am

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

Comment from Felix Marusic
Time: Friday, January 16, 2009, 10:25 am

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.

Comment from Felix Marusic
Time: Friday, January 16, 2009, 5:30 pm

Jon,

Thanks I’ll look into it.

Felix

Comment from Doug Glancy
Time: Tuesday, March 3, 2009, 8:10 pm

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.

Comment from Doug Glancy
Time: Tuesday, March 3, 2009, 9:54 pm

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!

Comment from Doug Glancy
Time: Tuesday, March 3, 2009, 10:32 pm

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.

Comment from NMullis
Time: Tuesday, May 12, 2009, 4:07 pm

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

Comment from NMullis
Time: Wednesday, May 13, 2009, 11:05 am

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

Thanks again! N

Comment from jeff weir
Time: Sunday, July 19, 2009, 5:36 am

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?

Comment from jeff weir
Time: Sunday, July 19, 2009, 5:53 am

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.

Comment from jeff weir
Time: Wednesday, July 22, 2009, 6:18 am

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.

Comment from Jeff Weir
Time: Wednesday, July 22, 2009, 6:54 pm

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

Comment from NMullis
Time: Tuesday, August 25, 2009, 3:43 pm

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

Comment from Barb Reinhardt
Time: Friday, September 11, 2009, 2:51 pm

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

Comment from Barb Reinhardt
Time: Saturday, September 12, 2009, 6:08 am

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

Comment from tables
Time: Thursday, January 21, 2010, 9:34 pm

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.

Comment from Matthew Parent
Time: Thursday, January 21, 2010, 9:36 pm

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

Comment from Steve Wypiszynski
Time: Friday, February 5, 2010, 10:27 am

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?

Comment from Samantha
Time: Friday, June 11, 2010, 6:27 am

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

Comment from Hans
Time: Tuesday, July 20, 2010, 1:55 pm

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.

Comment from Kabir
Time: Sunday, August 8, 2010, 5:13 am

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

Comment from Avis
Time: Sunday, August 8, 2010, 1:51 pm

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

Comment from avis
Time: Tuesday, August 10, 2010, 8:26 am

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.

Comment from avis
Time: Thursday, August 12, 2010, 10:14 am

Thank you very much… problems solved.

Comment from Joe
Time: Thursday, August 26, 2010, 10:26 am

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

Comment from Joe
Time: Monday, August 30, 2010, 5:53 am

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

Comment from Joe
Time: Monday, August 30, 2010, 8:17 am

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

Comment from Joe
Time: Monday, August 30, 2010, 11:45 am

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

Comment from Joe
Time: Tuesday, August 31, 2010, 1:51 am

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

Comment from Joe
Time: Tuesday, August 31, 2010, 3:30 am

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

Comment from Joe
Time: Tuesday, August 31, 2010, 7:03 am

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