CODEWITHSUNDEEP
excelvba
Logan
Logan

Reputation: 3

Automatically Update a Pivot Table Based Based on Cell Reference

I have a pivot table with the following columns:
item_number, job_number, description and qty.

I would like to have VBA code take the input of a cell, A5, to automatically sort through the job_number column of my pivot table and find the entries that have the job_number found within A5.

The data set is very large, so it is not a wise idea to sort through using PivotItems.

Upvotes: 0

Views: 155

Answers (2)

Toddleson
Toddleson

Reputation: 4457

I have a file that does exactly that. I have a cell which isn't part of the pivot table but acts like a filter field for the pivot table.

I put a script into the Worksheet_Change event to watch that cell for changes and a sub which swaps out the filter on the pivot table to match the cell value.

The Worksheet_Change event is basically like

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A5")) Is Nothing Then Call FilterChanger
End Sub

And the FilterChanger sub is basically like

Private Sub FilterChanger()
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Dim FilterText as String
    FilterText = Me.Range("A5")

    With Me.PivotTables("MyPivotTable")
        .ManualUpdate = True
        .PivotFields(3).ClearAllFilters
        .PivotFields(3).PivotFilters.Add Type:=xlCaptionContains, Value1:="zzzzzzzzzzzz" 
        'Filter out all results to prevent table updating when clearing filters

        .PivotFields(5).ClearAllFilters 
        'now that there is another filter, there isnt any lag while the table tries to load all the unfiltered data

        .PivotFields(5).PivotFilters.Add Type:=xlCaptionEquals, Value1:=FilterText, Value2:=FilterText
        .PivotFields(5).ShowDetail = True
        .PivotFields(3).ClearAllFilters
        .ManualUpdate = False
    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

In this code PivotFields(3) is any pivot row field that is in the table other than the one you intend to filter. and PivotFields(5) is the field that I am targetting with my filter. You can put column names instead of a number when referring to your fields, but I just prefer numbers.

In my file, the source data for the pivot table is 20k rows and growing, so I found that whenever I did .ClearAllFilters the workbook would pause for ~10 seconds while the pivot table attempted to load all 20k rows. To fix that issue, I put in a temporary filter in another field which will prevent any rows from loading while I swap out the filter in the intended field. I chose zzzzzzzzzzzz in PivotField(3) because it will always return 0 rows.

Upvotes: 1

Capt.Krusty
Capt.Krusty

Reputation: 627

Sub Macro1()
    ' in A1 there is the value for sorting
    ' if you want to show the whole table again run only the "select all" part (line 2):

    ' Field value == Column (right now column 2); Criterial == Filter Value (value of cell A1) ; change Table name to yours
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:=Range("A1").Value
    

    ' "Select All -- to show whole table again de-comment following line
    'ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2
    
End Sub

Hope this is what you are looking for.

Upvotes: 0

Related Questions