PDA

View Full Version : pivot table filters problem



ajm
09-15-2011, 08:36 PM
before i proceed, i would like everyone to know that I have also posted this question on MrExcel at http://www.mrexcel.com/forum/showthread.php?p=2865383#post2865383 but think that i shot myself in the foot by the way i worded it. Hopefully, someone here, who is not a member of mrexcel or who hasn't seen my post over there, might be able to help me with my issue.

i have a piece of code (below) which changes the report filter and the filter on the first row field based on selections made by the user from two validation lists - Account Manager (field name is "Master Account Manager") and Customer (field name is "Debtor Normal Name"). the second validation list is dependent upon the first.

Each time a user changes the Account Manager, I want the second validation list to default to "All" so that all the customers for that account manager are selected in the two pivot tables. Currently, they can only select one customer at a time.

The Worksheet is called Annual Results
The critical range where the validation lists are held: C2 Master Acc Manager
C3 Customer

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim critRange As Range, c As Range, ws As Worksheet, PT As PivotTable
Set critRange = Range("C2:C3")
If Intersect(Target, critRange) Is Nothing Then GoTo CleanUp

Dim pi As PivotItem
Dim i As Long, j As Long
Dim strFields() As String, strValue As String
Dim graphSheets As Variant
strFields = Split("Master Account Manager;Debtor Normal Name", ";")
Set graphSheets = Sheets(Array("Rep Sales Data", "TY Sales Data"))

On Error GoTo CleanUp
Application.EnableEvents = False
Application.ScreenUpdating = False

For Each ws In graphSheets
For i = 1 To critRange.Rows.Count ' loops twice first looks at C2, next 'looks at c3
strValue = critRange(i).Value
For Each PT In ws.PivotTables
' .ClearAllFilters
With PT.PivotFields(strFields(i - 1))
Select Case .Orientation
Case xlPageField
.ClearAllFilters
For Each pi In .PivotItems
If pi.Value = strValue Then
.CurrentPage = strValue
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi

Case Else

.PivotItems(strValue).Visible = True

For j = 1 To .PivotItems.Count
If .PivotItems(j) <> strValue And _
.PivotItems(j).Visible = True Then
.PivotItems(j).Visible = False
End If
Next j
End Select
End With
Next PT
Next i
Next ws


Application.Calculate
CleanUp:

Application.EnableEvents = True
End Sub

so, how do i get 'All" to show up in the second drop down box once the first box changes. At the same time, this should remove any existing filter from the customer field in both pivot tables.