asked on

Excel 2007 Change Pivot Filter Based On 2 Dynamic Variables

Hello,

Please see attached sample. Im hoping someone can provide a VB code that when ran will change the filters on the pivot table in sheet1 to what is reflected in cells B10 and E10 on the calculator tab. The macro should only change this pivot table so if there are others on the calculator tab they will not be affected.

Thanks!
EE-Sample.xlsx

nutsch

Here is code to put in the calculator sheet worksheet module, after naming the office and location dropdowns clOffice and clLocation

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pi As PivotItem

If Not Intersect(Target, Range("clLocation")) Is Nothing Then
    With Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("Location")
        .ClearAllFilters
        .CurrentPage = Range("clLocation").Value
    End With
End If

If Not Intersect(Target, Range("clOffice")) Is Nothing Then
    With Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("Department")
        For Each pi In .PivotItems
            If pi.Name = Range("clOffice") Then pi.Visible = True
        Next
        For Each pi In .PivotItems
            If pi.Name <> Range("clOffice") Then pi.Visible = False
        Next
    End With
End If

End Sub

Open in new window

See attached file for operation

Thomas
filterpivot.xlsm

Escanaba

ASKER

Its generating a 'method range of object worksheet failed error'.

Escanaba

ASKER

Ok...just caught the comment about naming the locations. Its now generating a different error as follows:

Cant execute code in break mode

at this point: If Not Intersect(Target, Range("clLocation")) Is Nothing Then

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Escanaba

ASKER

Thank you!