VBA - pivot table top 10 filter based on cell value

[thomas819]

Hello all,
I am currently at lost... I can´t figure out how to do seemingly easy task of filter top n values in pivot table base on another cell value.

When I recorded macro in hope to see what I have to tweak in order to set up this filter... I was shocked... When I changed Value1 Macro didn´t run at all. I spent a few hours digging online for solution...
Unfortunately non of them fit my case...

I hope someone will help me with my problem

Here is my recorded macro:
Sub Top_Filter()
Range("B11").Select ´I clicked on first column in my pivot table
ActiveSheet.PivotTables("TOP").PivotFields( _
"[sumarizácia].[Celé meno].[Celé meno]").PivotFilters.Add2 Type:=xlTopCount, _
DataField:=ActiveSheet.PivotTables("TOP").CubeFields( _
"[Measures].[Súcet Prac. dni mimo práce]"), Value1:=20 ´when I changes Value:=20 to whatever number... macro didn´t run at all... I get this msg: Run-time error ´1004´
End Sub

I will be grateful for any help you can give me.
Thank you in advance.

Best regards,
Tomas

 

[thomas819]

Sub Top_Filter()
Range("B11").Select                                                                                ´I clicked on first column in my pivot table
ActiveSheet.PivotTables("TOP").PivotFields( _
"[sumarizácia].[Celé meno].[Celé meno]").PivotFilters.Add2 Type:=xlTopCount, _
DataField:=ActiveSheet.PivotTables("TOP").CubeFields( _
"[Measures].[Súcet Prac. dni mimo práce]"), Value1:=20                                             ´when I changes Value:=20 to whatever number... macro didn´t run at all... I get this msg: Run-time error ´1004´
End Sub

 

[thomas819]

Could anybody help me with this issue? I am lost... I believe, that everything I need is to somehow declare variable let say value which I will input in cell A1.... Then I have no idea how to tell my pivot table to filter Top (A1.Value).

Please... I am a quite desprate to find solution to this seamingly simply problem... Simple problem at first glance... Then I find out, it is beoynd me ability.

 

[thomas819]

Please, can anybody help me? I am really in lost in VBA.
I will be grateful for any help which you can provide to me in this topic.

ActiveSheet is sheet named Calc

 

[thomas819]

For some reason this VBA still doesn't work...

Sub Makro1()

Dim x As Worksheet
Set x = ThisWorkbook.Sheets("Kalkulácie")

Dim var As Variant
var = x.Range("C1").Value

    x.PivotTables("TOP").PivotFields( _
        "[sumarizácia].[Celé meno].[Celé meno]").ClearAllFilters
    x.PivotTables("TOP").PivotFields( _
        "[sumarizácia].[Celé meno].[Celé meno]").PivotFilters.Add2 Type:=xlTopCount, _
        DataField:=x.PivotTables("TOP").CubeFields( _
        "[Measures].[Súcet Prac. dni mimo práce]"), Value1:=var
End Sub

I got this error:
Run-time error '9':
Subscrpit out of range

 

[thomas819]

Solution was surprisingly straight forward

Sub Top10filter()

    ActiveSheet.PivotTables("Top").PivotFields("[sumarizácia].[Celé meno].[Celé meno]").ClearAllFilters
    ActiveSheet.PivotTables("TOP").PivotFields( _
        "[sumarizácia].[Celé meno].[Celé meno]").PivotFilters.Add2 Type:=xlTopCount, _
        DataField:=ActiveSheet.PivotTables("TOP").CubeFields( _
        "[Measures].[Súcet Prac. dni mimo práce]"), Value1:=Range("C1").Value
End Sub

I just need add ClearAllFilters clause