none
Updating Pivot filter based on cell value RRS feed

  • Question

  • Hello, 

    VB  Beginner here.

    It may seem very trivial for you but I have spent too much time trying to figure this out. I am using a code from MSDN to update my pivot filter (https://blogs.msdn.microsoft.com/gabhan_berry/2008/01/31/using-cell-text-to-filter-pivottables/). I paste the below code by right-clicking on sheet and "view code". But it does not run. I assume there is some code change I am missing. Can someone please highlight the places where i should type in my worksheet/pivot table/field names.

    TIA

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        If Not Intersect(Target, Application.Range(RegionRangeName)) _
            Is Nothing Then
                UpdatePivotFieldFromRange _
                RegionRangeName, PivotFieldName, PivotTableName
        End If
    End Sub 
    
    Public Sub UpdatePivotFieldFromRange(RangeName As String, FieldName As String, _
    PivotTableName As String)
    
    
        Dim rng As Range
        Set rng = Application.Range(RangeName)
        
        Dim pt As PivotTable
        Dim Sheet As Worksheet
        For Each Sheet In Application.ActiveWorkbook.Worksheets
            On Error Resume Next
            Set pt = Sheet.PivotTables(PivotTableName)
        Next
        If pt Is Nothing Then GoTo Ex    
        On Error GoTo Ex
        
        pt.ManualUpdate = True
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        Dim Field As PivotField
        Set Field = pt.PivotFields(FieldName)
        Field.ClearAllFilters
        Field.EnableItemSelection = False
        SelectPivotItem Field, rng.Text
        pt.RefreshTable
        
    Ex:
        pt.ManualUpdate = False
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End Sub
    
    
    Public Sub SelectPivotItem(Field As PivotField, ItemName As String)
        Dim Item As PivotItem
        For Each Item In Field.PivotItems
            Item.Visible = (Item.Caption = ItemName)
        Next
    End Sub



    Wednesday, August 30, 2017 7:29 PM

Answers

  • Hi wandering_angel,

    Have your replaced RegionRangeName,PivotFieldName,PivotTableName as your actually range name, field name, table name?

    Workbook.SheetChange event is for the whole sheet in the workbook and it should be written under ThisWorkBook module, I think maybe what you need is WorkSheet.Change, you could select to use event for your indeed.

    Here is the example.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Application.Range("MyRange")) _
    
            Is Nothing Then
    
                UpdatePivotFieldFromRange _
    
                "MyRange", "LOCATION", "PivotTable1"
    
        End If
    
    End Sub

    Best Regards,

    Terry

    Thursday, August 31, 2017 6:15 AM

All replies

  • Hi wandering_angel,

    Have your replaced RegionRangeName,PivotFieldName,PivotTableName as your actually range name, field name, table name?

    Workbook.SheetChange event is for the whole sheet in the workbook and it should be written under ThisWorkBook module, I think maybe what you need is WorkSheet.Change, you could select to use event for your indeed.

    Here is the example.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Application.Range("MyRange")) _
    
            Is Nothing Then
    
                UpdatePivotFieldFromRange _
    
                "MyRange", "LOCATION", "PivotTable1"
    
        End If
    
    End Sub

    Best Regards,

    Terry

    Thursday, August 31, 2017 6:15 AM
  • Thanks Terry, that worked. The pivot refreshes to change the filter but it is selecting multiple items instead of the entered value. Any thoughts?
    Thursday, August 31, 2017 10:45 AM