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 ?

 

[Jon Peltier]

It's not always a good idea to put code like this into a sheet's code module.

How are you invoking the code? I have the code in a regular module and the chart on a worksheet. A forms menu button is assigned to the macro. The macro references the active chart, so if I remember to select the chart first, it works fine.

Note that you have an extra End Select later in the code, which caused your earlier problem. Since you posted back, I assume that you fixed it.

 

[vane0326]

Try this!

Private Sub Worksheet_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
                   .DataLabels(intPoint).Top = .DataLabels(intPoint).Top - 20
               Next
           End With
       Next
    End With
End Sub

 

[Caliche]

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

I must do a similar work every month (generate aprox. 500 charts and copy them to PP). Could you please tell me how are you doing this or show me the macro that you are using?.

Thanks a lot.

Caliche

 

[Jon Peltier]

If the individual macro works, replace this:

With ActiveChart
... blah blah
End With

with this for embedded charts on one worksheet

For Each ChtOb in ActiveSheet.ChartObjects
With ChtOb.Chart
.. blah blah
End With
Next

or this for separate chart sheets:

For Each Cht in ActiveWorkbook.Charts
With Cht
... blah blah
End With
Next

or this for all charts on all worksheets

For Each Sht in ActiveWorkbook.Sheets
For Each ChtOb in Sht.ChartObjects
With ChtOb.Chart
.. blah blah
End With
Next
Next

 

[MJK]

Vane

Ran into a problem with that code.

Code was pasted into the sheet (I was going to mirate to a module next)

Runtime error 91
Object variable or or with block variable not set

VBE Hightlights this line
For intSeries = 1 To .SeriesCollection.Count

Jon - thats again for the help

Caliche - Here is the code Jon sent to bult load charts to PP. Its a show stopper. I did 500 charts while drinking a cup of coffee yesterday morning.

http://www.mrexcel.com/board2/viewtopic.php?t=173521&highlight=mjk

Thanks again for the help
MJK

 

[Caliche]

Vane


Caliche - Here is the code Jon sent to bult load charts to PP. Its a show stopper. I did 500 charts while drinking a cup of coffee yesterday morning.

http://www.mrexcel.com/board2/viewtopic.php?t=173521&highlight=mjk

Thanks again for the help
MJK

MJK: the only code from Jon Peltier that I see in the link you sent me is the code in the post from Jon that begins with

==================================
If the individual macro works, replace this:

With ActiveChart
... blah blah
End With

==================================

But I don't see in this code how to automate the copy of 500 charts fro Excel to PP.

Thanks for your help.

Caliche

 

[Jon Peltier]

Ran into a problem with that code.

Code was pasted into the sheet (I was going to mirate to a module next)

Runtime error 91
Object variable or or with block variable not set

VBE Hightlights this line
For intSeries = 1 To .SeriesCollection.Count

1. Put the code into a regular module.

2. Was the chart on the same sheet as the code was behind? (This is one reason for point #1). Does the code have some way to know what .SeriesCollection refers to? Such as a statement like

With ActiveChart

above it?

 

[Jon Peltier]

MJK: the only code from Jon Peltier that I see in the link you sent me is the code in the post from Jon that begins with

==================================
If the individual macro works, replace this:

With ActiveChart
... blah blah
End With

==================================

But I don't see in this code how to automate the copy of 500 charts fro Excel to PP.

There are more comprehensive samples on my web site:

http://peltiertech.com/Excel/XL_PPT.html

 

[MJK]

Okay - I'm making some progress with Vane's macro. The values are holding as the pivot cycles, which is great. I changed the bar color to 4, which is light green, but the chart still reverts to the defauly blue. Any idea why this is happening ?

Thanks for the help

MJK

 

[Jon Peltier]

This is a known issue in pivot tables and pivot charts: formatting customizations are lost upon refreshing of the pivot table. You could include in your code the commands to reapply the desired formats.