none
Changing multiple pivot tables by updating cells RRS feed

  • Question

  • Hi everyone

    I've been successfully using the code below to update a pivot table in one of my workbooks.

    What I would like to do now is update additional pivot tables on the same sheet (at the moment this code only updates PivotTable4 on the Pivot sheet). Specifically I would like cell $B$2 to update PivotTable5, cell $C$2 to update PivotTable6 and finally $D$2 to update PivotTable7 (all on the same Pivot sheet).

    Your help would be really appreciated!

    BO

    Private Sub Worksheet_Change(ByVal Target As Range)
         If Target.Address = "$A$2" Then FilterPivot Target.Value
         
     End Sub
    
    
    Sub FilterPivot(FV As Variant)
    
    'Move the ScreenUpdating here
    Application.ScreenUpdating = False
    
     Dim pi As PivotItem
     With Sheets("Pivot").PivotTables("PivotTable4").PivotFields("EmployeeID")
     
        For Each pi In .PivotItems
             If InStr(1, UCase(pi.Name), UCase(CStr(FV))) < 1 Then
                 pi.Visible = False
             Else
                 pi.Visible = True
             End If
         Next pi
     End With
    
    'Remeber to turn this property on after processing
    Application.ScreenUpdating = True
    
    End Sub
    
    Tuesday, April 24, 2012 3:30 PM

All replies

  • It is a bit difficult to create the test scenario.Could you pls upload in some file sharing site(Sendpace/Mediafire/Skydrive etc)

    I suggest to give the name of A2 as PivotTable4,$B$2 to PivotTable5, $C$2 to PivotTable6 and  $D$2 to PivotTable7.

    Private Sub Worksheet_Change(ByVal Target As Range)
         If Not (Application.Intersect(Target, Range("a2:d2")) Is Nothing) _
            Then FilterPivot Target
    End Sub

    Sub FilterPivot(FV As Range)

        'Move the ScreenUpdating here
        Application.ScreenUpdating = False
       
        Dim pi As PivotItem
       
        With Sheets("Pivot").PivotTables(FV.Name.Name).PivotFields("EmployeeID")
            For Each pi In .PivotItems
                If InStr(1, UCase(pi.Name), UCase(CStr(FV.Value))) < 1 Then
                     pi.Visible = False
                Else
                     pi.Visible = True
                End If
            Next pi
        End With
       
        'Remeber to turn this property on after processing
        Application.ScreenUpdating = True


    End Sub

    Wednesday, April 25, 2012 7:43 AM
    Answerer