Data validation to control a pivot table field

F

FA

I have the following VBA. I use it to change a Pivot table with a data
validation cell. The problem that I have is that when the pivot table should
show "All" it will overwrite the latest selection.

How can I change that in order to make it select "All" when required?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("k1")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Worksheets("Sales").PivotTables("Pivottable1") _
.PageFields("Alpha Name --------------------").CurrentPage =
Target.Value
Application.EnableEvents = True

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top