Please can you help with the following problem which has had me stumped for the past day?
hope I can explain it sufficiently for you to give me guidance on the correct way forward? Here goes..
created a pivotchart and placed it on (say) sheet "GRAPH". Excel has created the associated pivottable and stored it on (say)
Because the formating of the graph is lost each time the pivotgraph is refreshed i.e page fields
changed, i have written a small macro to reformat it which i have put in the "Private Sub Worksheet_PivotTableUpdate(ByVal
Target As PivotTable)" event in the "PIVOT" sheet. All appeared to work well.
However, on closer investigation, i
noticed that the pivotchart wasn't being updated (even though the source pivottable was set to refresh on open - another
problem for another day??) so on the pivotchart sheet i had to press the little red exclamation mark to update it and the
chart changed before my eyes. I thought I would then record a macro to see what i had to do.
The code the macro
recorder gave was "ActiveChart.PivotLayout.PivotTable.RefreshTable" so i simply inserted this into the chart.activate event
of the "GRAPH" sheet and assumed it would kick in everytime the graph was activated.
However, it crashes everytime
with an error message "unable to get the pivotfields propety of the pivottable class". If I put an msgbox in the relevant
"Sub Worksheet_PivotTableUpdate" to display the target.name, its blank!
I've tried all sorts of work arounds like
trying to update the pivottable in the graph activate event:-
dim pt as pivottable : set pt = ......... : pt.refreshtable etc
select the sheet before hand etc, etc but no joy.
Following the code through debug it always crashes with the same
message. If I manually update the pivottable on the PIVOT sheet (right click "refresh data") the table gets refreshed which
then triggers my graph reformat code and everything is fine.
I'm sure i'm doing something wrong but it doesn't
appear to like the chart and table being on different sheets?
I hope you have followed my words above but
essentially my question is how can i update my pivotchart (through code) based on a pivottable on another sheet?
really look forward to your replies.
Thanks in anticipation,
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)