Keepintg pivot table formatting during refresh

[ashleywanless]

Hi,

Have some pivot tables, linked to charts which refresh automatically upon opening.

Only problem is the table's loose the color formats from the bars, and sometimes the scales are not correctly adjusted. Does anybody know the code for an event macro which will cause the graphs to be re-formatted how i had them before everytime a pivot refresh action happens? It is possible the pivots are updated more often that on just opening which is why i need a macro based on a pivot refresh event.

Thanks

 

[Andrew Poulsom]

I think you will find that the Calculate event fires when a pivot table is refreshed.

 

[ashleywanless]

so if i use a calculate event this should work? if i insert this as a private macro into the workbook this would work for all pivot tables?

Thanks

 

[Andrew Poulsom]

In ThisWorkbook the procedure would be Workbook_SheetCalculate.

 

[ashleywanless]

thanks do you have any code around this?

 

[Andrew Poulsom]

The procedure stub would be:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
 
End Sub

which you can get by clicking the appropriate dropdowns at the top of the code window in the VBE.

 

[ashleywanless]

Hi,

thanks for that. still learning to use vbe.

So the dropdowns at the top contain all event types?

If i then just paste in some code of me recording the formatting of the graph would that work?

Thanks

 

[wigi]

So the dropdowns at the top contain all event types?

Yes.

If i then just paste in some code of me recording the formatting of the graph would that work?

Yes, though code of the macro recorder very often needs to get cleaned up, and made generic (e.g. add constants to avoid having the same thing appearing too many times in the code like sheet names, ranges, ...)

 

[ashleywanless]

Thanks for your help with this. Sorry for the lare reply i have been on holiday. Final code looks like this, although the last line to do with column B is just an autofit.

Private Sub Worksheet_Calculate()
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 11
.Pattern = xlSolid
End With
ActiveWindow.Visible = False
Windows("Legal Incidents Dashboard.xls").Activate
Columns("B:B").EntireColumn.AutoFit
End Sub