Filter Pivot table to blank if value doesnt match target

jacinthn

Board Regular
Joined
Jun 15, 2010
Messages
96
i have this code that works for filtering a pivot table to the cell value in b3, I am trying to figure out a way to filter it to (blank) if the cell value in B3 is not in .PivotFields("MGR").because rigth now it just leaves the previous values that the pivot was filtered to when the selected b3 cell value is not included in the list of items for ("MGR")... HELP please

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Sheets("MainPage").PivotTables("PivotTable4").PivotCache.Refresh
With Worksheets("MainPage").PivotTables("PivotTable4")
.PivotCache.Refresh
.PivotFields("MGR").CurrentPage = Target.Value

End With

Application.EnableEvents = True
End Sub
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,214,896
Messages
6,122,132
Members
449,066
Latest member
Andyg666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top