Pivot Charts (Excel 2000)

Topic

New Reply

Good Afternoon All,

I’ve got some data in a database layout and I want to create a pivot chart (see the attached).

I’ve modified the chart so that it looks exactly like I want it to look (ratio is using the left axis and is a line, count is using the right axis and is a bar chart).

Now I want to flip between the different factors and look at the chart.

When I do this, the chart refers to how is started and I need to go through the process of putting things back how I want them – sigh.

Any suggestions on stopping this annoying behaviour?

Reply | Quote

Replies

Edited to remove schoolboy error from code!!

Tim,
Pivot charts don’t retain their formatting when you change the data view, or change anything on the pivot table. The Help helpfully suggests you write your own macro to deal with formatting. This is one way, using the Calculate event on the chart sheet; paste the code into the chart’s VBA module:

Private Sub Chart_Calculate()

‘stop screen flicker
Application.Screenupdating = False

On Error Resume Next

‘change the formats to whatever you desire
With ActiveChart
.SeriesCollection(1).AxisGroup = 2
.SeriesCollection(2).ChartType = xlLineMarkers
End With

Application.Screenupdating = True

End Sub

HTH!

Graeme

Reply | Quote