Change Pivot's Value Filter (Top 10) for value in cell

[Jurre90]

Hello all,
First of all thanks for all the help that you guys have been giving me, without you knowing since i only browsed the forum. But now i have got a question that i cant find the answer to. Here is my situation:

I have got a pivot table in a worksheet which has got a value filter to only show the Top 10. But i would like to be able to change the number of results it shows, so showing Top 5 or Top 4 instead of Top 10. (I know that i can adjust this through the options of the pivot table) But i would like that it changes for the value that i fill in, in for example A1. So when i type 3 in cell A1, the pivot table changes and shows only the Top 3. Subsequently, when i type in 7 in cell A1, the pivot table changes and shows the Top 7, etcetera.

At the moment i am using the following Macro:

ActiveSheet.PivotTables("PivotTable_Batch_CC").PivotFields("Type fout").PivotFilters.Add _
Type:=xlTopCount, DataField:=ActiveSheet.PivotTables("PivotTable_Batch_CC"). _
PivotFields("Count of Type fout"), Value1:=5

But this macro only adds a filter, so ones its added, it cant be added again.

Can my option be done? Or is it possible to build a macro that first deletes the current filter and then adds a new one.

I hope my situation is clear and someone can help me. If you need more information for this, i would like to hear so.

Many thanks in advance!
Jurre

 

[Jurre90]

I did found a way to make it change according to the value in cell A1:

I ended the macro with:
PivotFields("Count of Type fout"), Value1:=Range("A2")

But now i still need to be able to adjust the filter instead of adding it. Or find a way to remove the filter before adding a new one.