PDA

View Full Version : Refresh pivots on close AND on data being entered



Immatoity
06-12-2009, 12:37 PM
pretty simple and I am sure I have asked this before and found out but alas I cannot find out where and cant locate the file ..

anyway..

excel file with 2 pivot tables "Income Pivot" and "Expense Pivot".. based on two other tabs in the workbook...

I want to

a) refresh both on the closure of the workbook and force a save.

b) refresh the pivot if and when items are added to the 2 source lists

Jan Karel Pieterse
06-15-2009, 01:30 AM
Add this code to the thisworkbook module:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oPT As PivotCache
For Each oPT In ThisWorkbook.PivotCaches
oPT.Refresh
Next
End Sub


And this code behind each sheet with a pivot table:

Private Sub Worksheet_Activate()
Me.PivotTables(1).RefreshTable
End Sub

Immatoity
06-15-2009, 03:18 AM
thanks