CODEWITHSUNDEEP
vbaexcelexcel-2007
asjohnson
asjohnson

Reputation: 1057

When the value in a cell changes change the filter on a pivot table

I have been reading around for a bit and reached the ask SO point. I have a drop down selector in excel that lets me change names and when the name is changed, the value in "C3" is changed (c3 references another cell on another sheet if that matters) and when the value in "C3" changes I want a pivot table on another sheet (sheet6 for now) to update its filter on territory id to reflect the new value of "C3".

It sounds like an event to me, so I went digging around and found out VBA has events (yay!), but I have been unable to get my event to actually do anything. The code I have there works when I run it as a macro, but I would really like it to automatically run everytime the value in cell "C3" changes.

Here is what I am trying so far:

 Sub Worksheet_Change(ByVal Target As Range)
 If Not Application.Intersect(Target, Sheets("Current Status").Range("C3")) Is Nothing Then
Sheets("Sheet6").PivotTables("PivotTable5").PivotFields("territory_id"). _
    ClearAllFilters
Sheets("Sheet6").PivotTables("PivotTable5").PivotFields("territory_id").CurrentPage _
    = Sheets("Current Status").Range("C3").Value
 End If
 End Sub

Update: I have found that if I put the above code in the sheet section (sheet 2) instead of in a new module I can get it to run if I physically enter the code and then hit enter. Now I am wondering if there is a way to make it do it without me having to manually enter the value and hit enter. I just want to be able to use my drop down menu to select a new name and when the value in c3 changes due to the drop down selector update the pivot table.

Thank you as always SO.

Upvotes: 2

Views: 20075

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

The problem is that C3 is not actually changing, because it's just formula reference that is updating. Is the "drop down selector" on a form or based on data validation and in a cell?

If it's based on a cell, set your target to be the target cell, not C3 -> because C3 is just a formula reference, and your drop down cell is the one actually changing.

If it's based on a form, but code in the on_change event of the form control.

Upvotes: 3

Related Questions