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