changing pivot table filters from data validation

[ajm]

hi all, i have set up a worksheet change event to use a certain value to then filter a pivot table. How do i tailor this to also use the second selection from the next cell to then filter the pivot table again? I have borrowed code from the contextures website to get this far:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim RepSalesData As Worksheet
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim strFieldName As String, strFieldGrp As String
   ' Dim critRange As Range
    'Dim AnnResults As Worksheet

    Set RepSalesData = Worksheets("Rep Sales Data")
    Set AnnResults = Worksheets("Annual Results")
    'Set critRange = AnnResults.Range("$C$2,$C$3")

    strFieldName = "Sales Rep Name"
    strFieldGrp = "Debtor Normal Name"

    On Error Resume Next
    With Application
        .EnableEvents = False
        .ScreenUpdating = False

        'If Not (Application.Intersect(Target, critRange) Is Nothing) Then
            If Target.Address = Range("c2").Address Then
                With RepSalesData.PivotTables("PivotTableRepSaleData").PageFields(strFieldName)
                    For Each pi In .PivotItems
                        If pi.Value = Target.Value Then
                            .CurrentPage = Target.Value
                            Exit For
                        Else
                            .CurrentPage = "(All)"
                        End If
                    Next pi
                End With
            End If

          .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

my drop down cells are on the worksheet "Annual Results" in C2, and C3. my pivot table is on the tab entitled "Rep Sales Data". this pivot table has Sales Rep Name as a report filter and Debtor Normal Name as the first column of names.

so, at this stage, when the first selection is made of Account Manager (C2), the pivot report filter changes also. I want the Customer Groups to change also based on the selection at C3.

I have tried joining two of contextures models ("Selection Change event to change pivot table report filter" and "Pivot MultiPages Change All 2007") to get the second event firing but no luck so far. can anyone point me in the right direction to get me started again?

 

[ajm]

Jerry, a further twist. Can the code ou supplied be amended to also change a second pivot table on another tab? I have been playing around with another lot of code from the contextures website for multiple pivot table changes but i can't get the two to be in sync.

 

[ajm]

Can anyone else provide some help on this one please?

 

[ajm]

solved - Re: changing pivot table filters from data validation

i worked it out with a little help from some other bits of code found around the place:

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 'Sheets(Array("Rep Sales Data", "TY Sales Data"))
        'With Worksheets("Rep Sales Data").PivotTables("PivotTableRepSaleData")
        For i = 1 To critRange.Rows.Count
            strValue = critRange(i).Value
            For Each PT In ws.PivotTables
            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

this changes the Report Filter on two separate pivot tables to whatever is selected in C2, and then filters the first row for whatever is selected in C3

again, many thanks to JS411 for getting me largely across the line.

 

[Jerry Sullivan]

Re: solved - Re: changing pivot table filters from data validation

Hello again Mate!

I've been on holiday for the past few weeks (in your beautiful Continent). So sorry to leave you hanging without a reply. It looks like you made some impressive progress- so maybe that wasn't such a bad thing in the long run.

I see that you have evolved the code since this thread and posted a different question. I'll take a look at your latest and see if I can offer any help.

Cheers!

 

[eyong123]

Hi I have a problem in pivot table and I wish to filter by using an input box where the user put the week numbers for which they want to filter and then on validate the input is used as filter criteria.

thanks in advance

 

[ajm]

have a look at the file on the contextures website using fazza's link in the last post of this thread:

http://www.mrexcel.com/forum/showthread.php?t=461337