Updating Pivotfields from a Data Validation list across multiple Pivot Tables on Multiple sheets

Carpenter447

New Member
Joined
Mar 5, 2012
Messages
10
Hello I have an issue that i have been playing around with my self for quite sometime now. I found a code that should work but for some reason it is not working and I haven't a clue why. Any help would be appreciated.

Goal : Code that will take a drop list and modify the pivot filters on multiple pivot tables on multiple sheets simultaneously. I.e. Drop list selection is Brown; Brown is part of the Filter Colors; All subsequent pivot tables with the same Filter for colors would then switch to brown and update the data within it.

Issue : The coding works on a sample set that I ripped from contexures.com but not for my data as it stands now.

Additional Info : I am running Excel 2007, I import that data within my pivots from an OLAP cube, & even though I work with macros quite often my actual verbatim writing skills are limited.

So the code below is an adapted version of what you can find on contextures website. I want to be able change three unique Pivot Fields simultaneously. The code string is simply triplicated below with the different fields inputted. When I use the initial code from contexture.com it works but then when i change the Pivotfield nothing happens.

Code:
Sub UpdatePivotFields()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField1 As String
Dim strField2 As String
Dim strField3 As String

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

'This sector changes strField1 or "Top Customer Name US"
strField1 = "Top Customer Name US"
    If Target.Address = Range("D2").Address Then
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField1)
                    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
            Next pt
        Next ws
    End If

'This sector changes strField2 or "_Plant"
strField2 = "_Plant"
    If Target.Address = Range("M2").Address Then
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField1)
                    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
            Next pt
        Next ws
    End If

'This sector changes strField3 or "Month"
strField3 = "Month"
    If Target.Address = Range("U2").Address Then
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField1)
                    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
            Next pt
        Next ws
    End If
End Sub
 
OK....I have another question. On one of my pivots, the field to filter has a different name than all the other pivot tables. On all the other pivots, the field to be filtered is "ShiptoCustomer Enterprise", but on one pivot table the field to be filtered is called "Enterprise Name". All pivot filters should be applied when Report Section tab cell B11 is changed. How do I get this filter applied to the last pivot where the field name differs?

sDV_Addr1 = "$B$11" 'Enterprise DV Cell

'---Make list of PTs to be Filtered
vPTNames = Array("Cust YoY!CustYoY", "CustRoll12!CustRoll", _
"Brand Trend!BrandTrend", "Cust-Brand Trend!CustBrand")
If Intersect(Target, Range(sDV_Addr1)) Is Nothing Or _
Target.Cells.Count > 1 Then Exit Sub

'---Set Parameters based on DV Cell changed
Select Case Target.Address
Case sDV_Addr1
sField = "[Ship To Customer].[Customer Enterprise]." & _
"[Customer Enterprise]"
sPage = "[Ship To Customer].[Customer Enterprise].&[" _
& Target & "]"
End Select
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
You could try the untested code below. The earlier code examples in this thread were based on having multiple DV dropdowns. I pared out much of the code that isn't needed for just one DV cell.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
     '---When the DV Dropdown is changed, sets CurrentPageName
     ' of corresponding PivotField for all listed PivotTables.
     Dim sField As String, sPage As String, i As Long
     Dim PT As PivotTable
     Dim vPTNames As Variant, vPTName As Variant

    
     Const sDV_Addr1 As String = "$B$11"  'Enterprise DV Cell

    
     If Target.Address <> sDV_Addr1 Then Exit Sub

    
     '---Make list of PTs to be Filtered
     vPTNames = Array("Cust YoY!CustYoY", "CustRoll12!CustRoll", _
        "Brand Trend!BrandTrend", "Cust-Brand Trend!CustBrand")

        
     '---Set Parameters based on each PivotTable
     On Error GoTo CleanUp
     Application.EnableEvents = False

    
     sPage = "[Ship To Customer].[Customer Enterprise].&[" _
                & Target & "]"

    
     '---Filter each PT in list using Parameters
     For i = LBound(vPTNames) To UBound(vPTNames)
        vPTName = Split(vPTNames(i), "!")
        Set PT = Sheets(vPTName(0)).PivotTables(vPTName(1))

        
        '---check which field name goes with this Pivot
        Select Case vPTName(i)
            Case "Cust-Brand Trend!CustBrand" 'exception
                sField = "[Ship To Customer].[Customer Enterprise]." & _
                    "[Customer Enterprise]"

            
            Case Else  'default
                sField = "[Enterprise Name].[Customer Enterprise]." & _
                    "[Customer Enterprise]"
            
        End Select

        
        With PT.PivotFields(sField)
            .ClearAllFilters
            On Error Resume Next
            .CurrentPageName = sPage
            On Error GoTo CleanUp
         End With
     Next i

    
CleanUp:
     Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0
Thanks so much for your help!!! Still running into issues with getting the Exception table to update. I believe I made the modifications to your code correctly below (see blue text) - 3 tables the source field to be changed is [Ship To Customer].[Customer Enterprise] on the Brand Trend table the field to be changed is [Enterprise].[Enterprise Name]. but because they are sourced from different tables, I'm thinking something needs to be modified with the sPage= section as well?!
'---Set Parameters based on each PivotTable
On Error GoTo CleanUp
Application.EnableEvents = False

sPage = "[Ship To Customer].[Customer Enterprise].&[" _
& Target & "]"

'---Filter each PT in list using Parameters
For i = LBound(vPTNames) To UBound(vPTNames)
vPTName = Split(vPTNames(i), "!")
Set PT = Sheets(vPTName(0)).PivotTables(vPTName(1))

'---check which field name goes with this Pivot
Select Case vPTName(i)
Case "Brand Trend!BrandTrend" 'exception
sField = "[Enterprise].[Enterprise Name]." & _
"[Enterprise Name]"


Case Else 'default
sField = "[Ship To Customer].[Customer Enterprise]." & _
"[Customer Enterprise]"

End Select
 
Upvote 0
You're right. I was forgetting that this was an OLAP source so the PivotItem's name also changes.

Try...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
     '---When the DV Dropdown is changed, sets CurrentPageName
     ' of corresponding PivotField for all listed PivotTables.
     Dim sField As String, sPage As String, i As Long
     Dim PT As PivotTable
     Dim vPTNames As Variant, vPTName As Variant

    
     Const sDV_Addr1 As String = "$B$11"  'Enterprise DV Cell

    
     If Target.Address <> sDV_Addr1 Then Exit Sub

    
     '---Make list of PTs to be Filtered
     vPTNames = Array("Cust YoY!CustYoY", "CustRoll12!CustRoll", _
        "Brand Trend!BrandTrend", "Cust-Brand Trend!CustBrand")

        
     '---Set Parameters based on each PivotTable
     
     On Error GoTo CleanUp
     Application.EnableEvents = False

    
     '---Filter each PT in list using Parameters
     For i = LBound(vPTNames) To UBound(vPTNames)
        vPTName = Split(vPTNames(i), "!")
        Set PT = Sheets(vPTName(0)).PivotTables(vPTName(1))

        
        '---check which field name goes this Pivot
        Select Case vPTName(i)
            Case "Cust-Brand Trend!CustBrand" 'exception
                sField = "[Enterprise].[Enterprise Name]." & _
                    "[Enterprise Name]"
                sPage = "[Enterprise].[Enterprise Name].&[" _
                    & Target & "]"

            
            Case Else  'default
                sField = "[Ship To Customer].[Customer Enterprise]." & _
                    "[Customer Enterprise]"
                sPage = "[Ship To Customer].[Customer Enterprise].&[" _
                    & Target & "]"
        End Select

        
        With PT.PivotFields(sField)
           .ClearAllFilters
           On Error Resume Next
           .CurrentPageName = sPage
           On Error GoTo CleanUp
        End With
     Next i

    
CleanUp:
     Application.EnableEvents = True
End Sub
 
Upvote 0
I really appreciate your help with this.

OK...this change only updates the first two pivot tables but not the last two.

I'm not sure if the order of the tabs that contain the pivots matter or not for the VBA code to work.

Currently the tabs/pivots are arranged in this order in the workbook
"Cust YoY!CustYoY" - pivot field to filter = "Ship To Customer.Customer Enterprise"
"CustRoll12!CustRoll" - pivot field to filter = "Ship To Customer.Customer Enterprise"
"Cust-Brand Trend!CustBrand" - pivot field to filter = "Ship To Customer.Customer Enterprise"
"Brand Trend!BrandTrend" - pivot field to filter = "Enterprise.Enterprise Name"

So the code works perfectly for the first two tabs/pivots Cust YOY and CustRoll12, but stops after that.

I confirmed my field names are correct for the last two tables by recording a macro of applying the filter manually.

Any thoughts? I will continue to play with it in the meantime.
 
Upvote 0
The order shouldn't matter since each SheetName!PivotName is being checked before assigning sPage and sField.

There is probably a slight mismatch in the names.
Try adding the two lines shown in blue font. It will send a display to the Immediate Window of your VB Editor to allow you to track the (mis)mapping of the names.

Code:
       '---check which field name goes this Pivot
        Select Case vPTName(i)
            Case "Cust-Brand Trend!CustBrand" 'exception
               [COLOR="#0000CD"] Debug.Print vPTName(i) & ": Exception"[/COLOR] 
               sField = "[Enterprise].[Enterprise Name]." & _
                    "[Enterprise Name]"
                sPage = "[Enterprise].[Enterprise Name].&[" _
                    & Target & "]"
            
            Case Else  'default
                [COLOR="#0000CD"]Debug.Print vPTName(i) & ": Default"[/COLOR]
                sField = "[Ship To Customer].[Customer Enterprise]." & _
                    "[Customer Enterprise]"
                sPage = "[Ship To Customer].[Customer Enterprise].&[" _
                    & Target & "]"
        End Select
If you still aren't able to fix the problem, please post the macro you recorded when manually applying the filters to the two Pivots that aren't working.
 
Upvote 0
OK....here is what I see in the immediate window
Cust YoY: Default
CustRoll: Default
These are the only two tables that are being updated by the program.

Here are the macros I recorded of the manual filter applied to the two that are not being updated
Macro 5 is for the table where the filter is being applied to the Ship To Customer. Customer Enterprise
Macro 6 is for the table where the filter is being applied to the Enterprise.Enterprise Name

Sub Macro5()
'
' Macro5 Macro
'

'
Sheets("Cust-Brand Trend").Select
ActiveSheet.PivotTables("CustBrand").PivotFields( _
"[Ship To Customer].[Customer Enterprise].[Customer Enterprise]"). _
ClearAllFilters
ActiveSheet.PivotTables("CustBrand").PivotFields( _
"[Ship To Customer].[Customer Enterprise].[Customer Enterprise]"). _
CurrentPageName = _
"[Ship To Customer].[Customer Enterprise].&[177_OPEN Chicago]"
End Sub
Sub Macro6()
'
' Macro6 Macro
'

'
Sheets("Brand Trend").Select
ActiveSheet.PivotTables("BrandTrend").PivotFields( _
"[Enterprise].[Enterprise Name].[Enterprise Name]").ClearAllFilters
ActiveSheet.PivotTables("BrandTrend").PivotFields( _
"[Enterprise].[Enterprise Name].[Enterprise Name]").CurrentPageName = _
"[Enterprise].[Enterprise Name].&[177]"
End Sub

I'm not sure I understand why Macro 6 is not displaying the full field description in the last line (177_OPEN Chicago) even though that is what is displayed on the screen.
[TABLE="width: 377"]
<COLGROUP><COL style="WIDTH: 189pt; mso-width-source: userset; mso-width-alt: 9216" width=252><COL style="WIDTH: 188pt; mso-width-source: userset; mso-width-alt: 9179" width=251><TBODY>[TR]
[TD="class: xl63, width: 252, bgcolor: transparent"][/TD]
[TD="width: 251, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #ddd9c3"]Sub Business Unit[/TD]
[TD="class: xl68, bgcolor: #ddd9c3"]Item Group[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Enterprise Name[/TD]
[TD="class: xl65, bgcolor: transparent"]177_OPEN Chicago[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
There is a mismatch between the field name and page name recorded by Macro5 and the last code we were trying.

Change the code to this and see if that fixes the CustBrand pivot:
Code:
        Select Case vPTName(i)
            Case "Cust-Brand Trend!CustBrand" 'exception
                Debug.Print vPTName(i) & ": Exception"
                sField = "[Ship To Customer].[Customer Enterprise].[Customer Enterprise]"
                sPage = "[[Ship To Customer].[Customer Enterprise].&[" _
                    & Target & "]"

Macro6 is showing that there is a mismatch in for the item name [177_OPEN Chicago] versus [177]
Is that mismatch only happening when "177_OPEN Chicago" is selected from the drop down, or is that consistent regardless of the item chosen?
 
Upvote 0
I can't thank you enough for your help!!

I tried pasting your revision above, but it did not work. I'm also confused why we are setting Cust-Brand Trend!Cust Brand as the exception. That table is being filtered on the Ship To Customer.Customer Enterprise (the same as the first 2). This table updated perfectly with the original code when all tables were referencing the "Ship To Customer.Customer Enterpise" field. Shouldn't the Brand Trend!BrandTrend table be the "exception" since that has been changed to filter based on another field Enterprise.Enterpise Name?

In response to your question for Macro 6, it looks like this is consistent regardless of which Enterprise is pulled. I guess what is happening is that there is a "display" name (which is what you see Enterprise Code [ie. 177]_Enterprise Name) versus what is really behind the scenes - which appears to be just the Enterprise Code [177]. So I guess this is what is causing the issue with applying the filter to the Brand Trend!BrandTrend table. Thoughts?
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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