Pivot TopCount Filter based on cell value

[GDunn]

Hi,

I am trying to use VBA to filter a pivot table value (Top 10 etc) based on a cell value on a seperate sheet.

1. Not sure if I reference Set pvtField = pvtTable.PivotFields("Employer_Name") or Set pvtField = pvtTable.PivotFields("Sum of Value")
2. The command filter (For Each pvtItem In pvtField.PivotItems _
pvtFields.xlTopCount _ pvtFields.Value = filterVal _ Next pvtItem) is causing me a headache.

Is this possible to do? Any help greatly appreciated.

Sub Top_Filter_1()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterVal As String

Set pvtTable = Worksheets("T RM LW").PivotTables("PivotTable6")
Set pvtField = pvtTable.PivotFields("Employer_Name")

filterVal = Worksheets("Data").Range("C3")

For Each pvtItem In pvtField.PivotItems
pvtFields.xlTopCount
pvtFields.Value = filterVal

Next pvtItem

End Sub

 

[Jerry Sullivan]

Hi GDunn,

For Top10 or TopX, it's easier if you use the PivotFilter property.
Try this untested code....

Sub Top_Filter_1()
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim filterVal As String
    
    Set pvtTable = Worksheets("T RM LW").PivotTables("PivotTable6")
    Set pvtField = pvtTable.PivotFields("Employer_Name")

    filterVal = Worksheets("Data").Range("C3")

[COLOR="Blue"]    pvtField.PivotFilters.Add Type:=xlTopCount, _
        DataField:=pvtTable.PivotFields("Sum of Value"), Value1:=10[/COLOR]

End Sub

 

[GDunn]

Thanks JS411. I tried something similar previously. This returns a runtime error.
Also, not sure how this filters based on the value in the desired cell. Shouldn't the DataField value be based on FilterVal?

 

[GDunn]

Thanks JS411. I tried something similar previously. This returns a runtime error as the filter already exists. I want to change the filter already in place.
Also, not sure how this filters based on the value in the desired cell. Shouldn't the DataField value be based on FilterVal?

 

[Jerry Sullivan]

My mistake. What does FilterVal represent?
Is it the number of items IE for Top10 FilterVal=10; or
All number greater than or equal to FilterVal (which might be more or less than 10);
or something else?

 

[GDunn]

filterVal references the cell which stores the value to be filtered (10, 20 etc.)
I cannot reference filterVal as the value for some reason. it asks for a number between 1 and 2######.

 

[Jerry Sullivan]

filterVal references the cell which stores the value to be filtered (10, 20 etc.)
I cannot reference filterVal as the value for some reason. it asks for a number between 1 and 2######.

I don't understand what you mean by the "Value to be filtered" based on your earlier description of Top10.

Are you trying to filter to show only the Data Value that is in filterVal (not values that are greater or less than)?

BTW if you'r using PivotFilters, you'll use ClearValueFilters to clear any previous filters.

 

[GDunn]

Thanks for your help.
The filterVal is the value of the Top filter. ie. top10, top20 etc.

This variation works if I use the full value field reference.

Sub Top_Filter()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim filterVal As String

Set pvtTable = Worksheets("T RM LW").PivotTables("PivotTable6")
Set pvtField = pvtTable.PivotFields("Employer_Name")
filterVal = Worksheets("Data").Range("C3")
Worksheets("T RM LW").PivotTables("PivotTable6").PivotFields("Employer_Name"). _
ClearAllFilters
pvtField.PivotFilters.Add Type:=xlTopCount, _
DataField:=pvtTable.PivotFields("Sum of Value"), Value1:=Worksheets("Data").Range("C3")

End Sub

 

[Jerry Sullivan]

Ahhh... now I understand what you meant.

You can either convert the String to a Long Data Type using

Value1:=CLng(filterVal)

or simply Declare filterVal as a Long Data Type.

Sub Top_Filter2()
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim filterVal As Long
    
    Set pvtTable = Worksheets("T RM LW").PivotTables("PivotTable6")
    Set pvtField = pvtTable.PivotFields("Employer_Name")

    filterVal = Worksheets("Data").Range("C3")

    With pvtField
        .ClearValueFilters
        .PivotFilters.Add Type:=xlTopCount, _
            DataField:=pvtTable.PivotFields("Sum of Value"), _
            Value1:=filterVal
    End With
End Sub

 

[GDunn]

Perfect !

Thank you