How to auto refresh Excel auto-filter results after Excel PivotTable auto update

0
votes

I have created a "Summary" tab using an Excel Pivot Table.

The Pivot Table shows the sums of selected values in various categories stored in a "Details" tab.

The Details tab contains...

Select?   Type   Price
   Y      Shoe   $1.25
   N      Shoe   $1.50
   Y      Boot   $2.00
   N      Clog   $3.00
   Y      Shoe   $0.50

The Summary tab shows a Pivot Table like this...

Select?       Y

Row Labels    Sum of Price
Boot          $2.00
Shoe          $1.75

Whenever something is changed in the Details tab, and someone switches to the Summary tab, I want the Pivot Table to auto update to match the new data.

This is easy if just the values change (using the first code snippet below), but if the Y and N change, the filtering does not update. Anything that has changed from N to Y is not shown until the filtering is manually updated.

This is what I have so far...

The code to auto-update a Pivot Table is e.g. ...

Private Sub Worksheet_Activate()
    Sheets("Summary").PivotTables("PivotTable1").RefreshTable
End Sub

The code to auto-refresh an Excel auto-filter column in a table (that's not a Pivot Table) is e.g. ...

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveWorkbook.Worksheets("Details").ListObjects("Table1")
         .AutoFilter.ApplyFilter
    End With
End Sub

...but I cannot figure out how to apply AutoFilter.ApplyFilter to a Pivot Table. Any ideas? Or Equivalent?

The nearest thing I found to touch the Pivot Table filter is this code which will turn the part I want to update yellow...

Worksheets("Details").PivotTables("PivotTable1") _
    .PageRange.Interior.Color = vbYellow

...but I don't want to turn it yellow :-) , I want to re-apply the filter to the modified data so I see what I should see, rather than the wrong values and missing rows.

Many thanks.

2
vbaexcelpivot-tableautofilter

2 Answers

0
votes

Two things I can think of... running a macro on activation or deactivation of a sheet. 

Private Sub Worksheet_Activate()
Worksheet_Change 'Macro name
End Sub

This would be tied specifically to the sheet for your summary. Similarly, you would use Worksheet_Deactivate() tied to your other sheet. You can also throw in the pivot table update to this same macro.

0
votes

The result of recording the macro (not updating the filter, but re-applying the choice of filtering by "Y") and editing it in is...

Private Sub Worksheet_Activate()
    ActiveSheet.PivotTables("PivotTable1").RefreshTable
    On Error Resume Next
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Selection") _
    .CurrentPage = "Y"
End Sub

It works for me. I had to add the On Error to (clumsily) catch the case where there was no "Y" to filter by.

If anyone needs a more generic solution to refresh the filtering without re-applying a specific filter choice, the above will not work, but that's no problem in my use case.

Many thanks.