Excel - Refreshing PivotTable data as Power Query auto refreshes PivotTable source

1
votes

I have a database which auto refreshes and updates the table from an external source every 15 minutes. I tried the following code which updates the PivotTable every time the source data is edit/added/deleted:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets("PIVOT TABLE WORKSHEET").PivotTables("PIVOT TABLE NAME").RefreshTable
End Sub

When i manually edit the source data, the PivotTables refresh accordingly. However, when the source data is updated automatically the PivotTables remain unchanged. Is there a way to make the Pivot tables refresh together with the database without the need of a user input?

1
excelvbapivot-table
I forget if this issue exists with Worksheet_Change, but that's what you'd want to use, not Worksheet_SelectionChange. – Doug Glancy

1 Answers

4
votes

You'll have better results if you use the Worksheet_Change event instead of the Worksheet_SelectionChange event, so that the procedure runs when data changes, not when you select a cell with the mouse or keyboard.

Worksheet_Change vs. Worksheet_SelectionChange events

  • Worksheet_SelectionChange fires when the selection changes on a worksheet.
    For example, when the user clicks on a cell, or pushes an arrow key.

  • Worksheet_Change fires when cells on the worksheet are changed, either by the user or by an external link.

Note: Worksheet_Change does **not** occur when cells change during a re-calculation; use theCalculate` event to trap a sheet re-calculation.)

Depending on how the data is laid out in your worksheet(s), you may want to limit the execution of this procedure by checking which cell(s) were changed, which is easiest overall by comparing the event procedure's Target parameter to a specific cell or cell-range, using the Intersect function.

Caution: Beware of infinity!

When using code that changes cells within the area being "watched" by the Worksheet_Change event procedure, you risk entering into an infinite loop, since the change fires the event again, which changes cells again, etc.


(Click image to enlarge.)

There are several ways this could be avoided. This most common is to temporarily disable events with the Application.EnableEvents property while the Change event does what it needs to do. (Don't forget to re-enable events at the end of the procedure... see example below.)

Example:

Here's an untested example using all these points:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const cellsToWatch = "A1:D4"
    With Worksheets("PIVOT TABLE WORKSHEET")
        'exit the procedure if at least part of the changed cells were not within `A1:D4`
        If Application.Intersect(.Range(cellsToWatch), Target) Is Nothing Then
            'the change wasn't within `cellsToWatch`
            Exit Sub
        End If
        Application.EnableEvents = False 'disable execution of this or other events

        '----------Run your code here:--------------
        .Calculate
        .PivotTables("PIVOT TABLE NAME").RefreshTable
        '-------------------------------------------

        Application.EnableEvents = True  're-enable events
    End With
End Sub

More Information: