changing pivot table filters from data validation

A

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,056
Office Version
  1. 365
Platform
  1. Windows
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:
Code: 
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hi ajm,

You can try the code below.
It's set up to handle the two fields you listed, and could be expanded to additional fields if needed.

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

    Dim pi As PivotItem, i As Long
    Dim strFields() As String
    strFields = Split("Sales Rep Name;Debtor Normal Name", ";")    
    On Error GoTo CleanUp
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    With Worksheets("Rep Sales Data").PivotTables("PivotTableRepSaleData")
        For i = 1 To critRange.Rows.Count
            With .PageFields(strFields(i - 1))
                .ClearAllFilters
                For Each pi In .PivotItems
                     If pi.Value = critRange(i).Value Then
                        .CurrentPage = critRange(i).Value
                        Exit For
                    Else
                        .CurrentPage = "(All)"
                    End If
                Next pi
            End With
        Next i
    End With

CleanUp:
    Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0
Jerry, the second event is not passing the customer group from the selection to the pivot table. that is, after i select the account manager ('Annual Results'!$C$2), the worksheet change events fires and the account manager's name is selected in the pivot table report filter. that's good. however, when then selecting the particular customer group in the next cell down ('Annual Results'!$C$3), the event fires but fails to make the change to the "Debtor Normal Name" field in the pivot table.

Using a breakpoint to step through the macro, i noticed that the pi value retains the account manager list rather than picking up the customer groups. i tried to set it back to nothing to see if clearing the variable would help but it did nothing.

any ideas where to llok next?
 
Upvote 0
The code's approach is to run the same process regardless of whether the first or second critieria is changed.

The process is:
1. Using .PageFields("Sales Rep Name")
... it steps through each PivotItem to find a match for the value in C2;

2. Using .PageFields("Debtor Normal Name")
... it steps through each PivotItem to find a match for the value in C3;

When you stopped the code at the breakpoint, on the first step it would have shown the Account Mgr list first; before moving on to the Debtors.

That doesn't explain why it didn't work correctly and set both filters for you, but it might help you in debugging.
 
Upvote 0
could it be the way my pivot table is set up compared to the code you have written? on the Annual Results worksheet, in C2 and C3, i have my two validation selections: Account Manager in C2, and Customer in C3. on the pivot table, Rep Sales Data, Sales Rep Name is the report filter (field name is in C2, and the actual account managers name is in D2), Debtor Normal Name is the first of two row labels and its in C6. all data in the pivot table is in rows 7 down. will this make a difference to your code?<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com) -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2" align="left">
<tr><th bgcolor="#B8CCE4" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Sales Rep Name</font></th><th bgcolor="#B8CCE4" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Santa Claus</font></th><th bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></th><th bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></th></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td></tr>
<tr><td colspan="2" rowspan="2" bgcolor="#B8CCE4" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#B8CCE4" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Values</font></td><td bgcolor="#B8CCE4" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">WeekNo</font></td></tr>
<tr><td bgcolor="#D8D8D8" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Sum</font></td><td bgcolor="#D8D8D8" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td></tr>
<tr><td bgcolor="#B8CCE4" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Debtor Normal Name</font></td><td bgcolor="#B8CCE4" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Debtor Name No</font></td><td bgcolor="#D8D8D8" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">1</font></td><td bgcolor="#D8D8D8" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">2</font></td></tr>
<tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">JACKS GIFTS</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">$81,001.56</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">$152,860.98</font></td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">JACKS PRESENT SHOP HOLDING AC (12345)</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">$80,685.06</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">$152,544.48</font></td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(JGA) JACKS GIFTS AUSTRALIA (987654)</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">$0.00</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">$0.00</font></td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">JACKS LTD (33555)</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">$316.50</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">$316.50</font></td></tr>
<tr><td bgcolor="#B8CCE4" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Grand Total</font></td><td bgcolor="#B8CCE4" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#B8CCE4" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">$81,001.56</font></td><td bgcolor="#B8CCE4" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">$152,860.98</font></td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td></tr>
</table>
 
Upvote 0
Yes, I thought you had two report (Page) filters.
Will you always have a Page filter in C2 and a Row filter in C3?
 
Upvote 0
yes. it will stay the way it is currently.
 
Upvote 0
Please give this a try....

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim critRange As Range, c As Range
    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
    strFields = Split("Sales Rep Name;Debtor Normal Name", ";")
    
    On Error GoTo CleanUp
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    With Worksheets("Rep Sales Data").PivotTables("PivotTableRepSaleData")
        For i = 1 To critRange.Rows.Count
            strValue = critRange(i).Value
            With .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 i
    End With

CleanUp:
    Application.EnableEvents = True
End Sub
 
Upvote 0
SOLVED: changing pivot table filters from data validation

JS411 said:
Please give this a try....

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim critRange As Range, c As Range
    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
    strFields = Split("Sales Rep Name;Debtor Normal Name", ";")
    
    On Error GoTo CleanUp
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    With Worksheets("Rep Sales Data").PivotTables("PivotTableRepSaleData")
        For i = 1 To critRange.Rows.Count
            strValue = critRange(i).Value
            With .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 i
    End With

CleanUp:
    Application.EnableEvents = True
End Sub
Click to expand...

Jerry, thats the one! many many thanks for your assistance over the last couple of days. I can now have a decent weekend playing with the kids rather than stuck indoors finishing off this model.
 
Upvote 0
Re: SOLVED: changing pivot table filters from data validation

ajm said:
Jerry, thats the one! many many thanks for your assistance over the last couple of days. I can now have a decent weekend playing with the kids rather than stuck indoors finishing off this model.
Click to expand...

:beerchug: Cheers Mate!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Pivot Table VBA
Replies
0
Views
238
chaddres
C
H
  • Solved
Macro to Filter Pivot Table
Replies
2
Views
848
howard
H
D
  • Question Question
VBA code to filter out blank values in a pivot table
Replies
2
Views
728
dragontbone
D
ghull99
  • Question Question
Attempting VBA to refresh "Chart Title" based on Pivot Filters. Filter1 works in VBA, but title doesn't update based on filter2.
Replies
0
Views
73
ghull99
ghull99
M
  • Question Question
VBA Macro with option to filter Pivot Tables multiple times.
Replies
0
Views
721
Macro_nerd
M

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

Share this page

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