Auto - Refresh Pivot Table

[informer30]

Hi All,

I have a Excel 2003 workbook which has a source sheet which I manually updatecut and paste. This source sheet then updates a sheet in the same workbook whichis part of a pivot table.

In the source sheet I append all new data.

The issue is when appending new data to the source sheet this is not picked up in the pivot table sheet unless I click 'Refresh'.

Is there a auto refresh option which automatically updates the pivot tablewith new data.

Many Thanks

 

[SkipVought]

Hi,

Paste this code in the SHEET CODE WINDOW (right-click the Sheet Tab > Select View Code)

Private Sub Worksheet_Activate()
    Dim pc As PivotCache
    For Each pc In ThisWorkbook.PivotCaches
        pc.Refresh
    Next
End Sub

After you append your data, activating the sheet containing your PivotTable will refresh.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Try this instead...

Private Sub Worksheet_Activate()
    Dim pc As PivotCache, pt As PivotTable, a, b, c, d
    For Each pt In ActiveSheet.PivotTables
        a = Split(pt.SourceData, "!")
        b = Split(a(1), ":")
        c = Split(b(0), "R")
        d = Split(c(1), "C")
        pt.SourceData = a(0) & "!" & Sheets(a(0)).Cells(CInt(d(0)), CInt(d(1))).CurrentRegion.Address(, , xlR1C1)
        pt.PivotCache.Refresh
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Does refreshing the PivotCache reliably refresh all pivot tables using that PivotCache?.. Or does one also need to
pt.PivotCache.Refresh for each table (as Skip's second code does).

I have a workbook with many pivottables. Refreshing each takes longer than I would like. Using the watch window I find that I have two PivotCaches though the Sourcedata is identical for each.

Gavin

 

[Gavona]

Further investigation suggests that refreshing the pivotcache like this:
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Does not refresh the pivottables

Refreshing one pivottable from each cache does refresh all the tables in the workbook. So I am using the following code. (Altering Verbose to True enables the msgbox lines to enable me to follow what is happening):

Sub RefreshAllPivotTables()
    Dim pc As PivotCache
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim LastRefresh
    Dim answer
    Dim verbose As Boolean
    
    verbose = False ' set to false to get rid of msgbox display
    LastRefresh = Now
    For Each pc In ThisWorkbook.PivotCaches
        pc.Refresh
        
    Next
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            'following line reports when last refreshed
            If verbose Then answer = MsgBox(ws.Name & pt.Name & ": " & Str(pt.RefreshDate), , "Checking when pivottable was last refreshed")
            If pt.RefreshDate < LastRefresh Then
                pt.RefreshTable
                If verbose Then answer = MsgBox(ws.Name & pt.Name & ": " & Str(pt.RefreshDate), , "Checking when pivottable was last refreshed")
            End If
        Next
    Next
    Set pt = Nothing
    Set ws = Nothing
    Set pc = Nothing
End Sub

But now I am trying to figure if there is a difference between these - if anyone knows I would be grateful.

pt.PivotCache.Refresh
pt.RefreshTable

Gavin