FILTERS inside Pivot Table does not update when source data (incl VBA) changes

[Johnhtvu]

Hi,

question about pivot table FILTERS (see picture)

Situation:
I erased my source data and my pivotable refreshes correctly as seen in the picture. this happens automatically as I am using a little VBA code in the source data to refresh my pivot tables automatically.

However, when you click on filter, you can still see the source data - while I already erased the source data

If I add new data to my source data - the item is not selected in the filters and therefore, does not appear in the pivot table.
Does anyone know what happens here?

Thanks.

 

[mart37]

Maybe you can use VBA to refresh the cache.
When your last item was deselected. All new items are als deselected. Also you can use VBA to select all the items.

 

[mart37]

Look also to Missing Items.

 

[Johnhtvu]

Update:
I figured that you need to ''select all' in the filter so that new information in the source data will be selected.
However, I had 'blank' deselected as I don't want that data in my pivotable.

However, I prefer to have it without the blanks.

see these 2 pictures.

Still not been able to solve why my pivotable refreshes but the information stays in the filters

 

[Johnhtvu]

Maybe you can use VBA to refresh the cache.
When your last item was deselected. All new items are als deselected. Also you can use VBA to select all the items.

Hi, I am not sure what you mean.

I am using this VBA code on my source data:

----------
Private Sub worksheet_change(ByVal target As Range)

ThisWorkbook.RefreshAll

End Sub
-------------

Pivottables and all work fine. However, I just don't understand why the values are still in my filters, while source data is empty.

 

[Johnhtvu]

I resolved the issues with the filters by doing this:

Pivot table options -> Data -> Deselect "retain items deleted from data source''

---

Problem remains: if source data is empty: the pivot table by default 'selects all' - including blanks.

Any way to exclude blanks by default?

 

[mart37]

Mabe you can use this:

    ActiveSheet.PivotTables("myPivottable").PivotCache.Refresh
    ActiveSheet.PivotTables("myPivottable").PivotFields("myField").ClearAllFilters
    ActiveSheet.PivotTables("myPivottable").PivotCache.MissingItemsLimit = xlMissingItemsNone
    For Each pField In ActiveSheet.PivotTables("myPivottable").PivotFields
        On Error Resume Next
        pField.PivotItems("(blank)").Visible = False
    Next

 

[Johnhtvu]

Mabe you can use this:

    ActiveSheet.PivotTables("myPivottable").PivotCache.Refresh
    ActiveSheet.PivotTables("myPivottable").PivotFields("myField").ClearAllFilters
    ActiveSheet.PivotTables("myPivottable").PivotCache.MissingItemsLimit = xlMissingItemsNone
    For Each pField In ActiveSheet.PivotTables("myPivottable").PivotFields
        On Error Resume Next
        pField.PivotItems("(blank)").Visible = False
    Next

I managed to go without vba by:

1) Pivot table options -> Lay out -> Allow multiple filters
2) Deselect retain item deleted from data source