Is there any way to make pivot charts look better

[MJK]

I've been expermenting with pivot charts. They work great, but all you can get when using a bar chart is a blue bar. I tried reformatting the chart, changing the bar colors, adding values - until it looks great. But once i refresh the data - I lose everything and am left with the default blue bars. Is there a work around ?

 

[vane0326]

Use this code and change the color code to your desire.

Private Sub Chart_Calculate()
    Dim intSeries As Integer
    Dim intPoint As Integer    
    With ActiveChart
       For intSeries = 1 To .SeriesCollection.Count
           With .SeriesCollection(intSeries)
               Select Case intSeries ' may need to extend cases
               Case 1
                   .Interior.ColorIndex = 41
               End Select
               .ApplyDataLabels
               .DataLabels.Position = xlLabelPositionInsideEnd
               .DataLabels.Font.Bold = True
               For intPoint = 1 To .Points.Count
                   End Select
                   .DataLabels(intPoint).Top = .DataLabels(intPoint).Top - 20
               Next
           End With
       Next
    End With
End Sub

 

[mrhartley]

that is a very fustrating feature - i have to record a macro of all the formatting changes required for each chart then assign said macro to a button so whenever the pivot tables get refreshed, its then a simple button push to get the charts back looking nice.

its a big pain too

i will try that code sometime vane

 

[MJK]

I have a couple of questions and need some direction

I pasted the code into a module. Changed the formatting of the pivot chart, and refreshed the data. The chart reverted back to the default blue bars. I looked to run the macro from Tools> Macros and there were none listed. Went back to the VBE and ran the macro and got a Compile Error: End Select without Select Case. What did I do wrong ?

Thanks again for the help

MJK

 

[tusharm]

Yes, that is a downside of pivot charts (and pivot tables). Several formatting options get reset each time the table/chart is refreshed.

What you should do is create custom code that makes the changes you want. To do that, select the PC, then turn on the macro recorder (Tools | Macro > Record new macro...), do the customization you want, and turn off the recorder. Now, earch time you redo the chart, select it and run the code.

Edit: Alternatively, you can do what I do often. Use the data from the pivot table but create my own 'regular' chart. Now, the chart refreshes whenever the PT does but it doesn't lose any formatting -- with the added benefit that there is no code to worry about.

I've been expermenting with pivot charts. They work great, but all you can get when using a bar chart is a blue bar. I tried reformatting the chart, changing the bar colors, adding values - until it looks great. But once i refresh the data - I lose everything and am left with the default blue bars. Is there a work around ?

 

[MJK]

I have gone this route in the past, and it works great if you only have a few charts. I have about 500 charts that need to be generated on a monthly basis and pasted into powertpoint. With the help of the board, I have been using a macro with cycles through all the data in a pivot chart, and bulk loads to chart picture into powerpoint. Could I record a chart formatting macro which will loop everytime the pivot is refreshed ?

Thanks for the hlep

MJK

 

[jonwah]

Does it not work if you right click on the chart, go to options and turn auto format table off? You shouldn't loose the formating when it is refreshed.

 

[MJK]

I see that option for the pivot table, but not for the chart.

The available chart options are Titles, Axes, etc.

Am I missing something ?

 

[vane0326]

Wait a minute did you go to the Pivot Chart tab right click and click on view code and paste the code in that window. It should work.

 

[MJK]

Vane

That's what the problem was - I moved my pivot chart to the same sheet where my pivot table resides. The code worked great when the pivot chart is on its oun sheet.

Is there a way of modifying the code so it will work when to pivot chart is moved to the same sheet as the pivot ?

Thanks
MJK