Filter pivot table off multiple reference cells?

[SURFER349]

So there's this tutorial to use a single cell
How to control Excel PIVOT tables from a cell value with VBA | Dedicated Excel

But what if I want to have multiple items filtering the pivot table? The below script is what I currently have. What if I want to use 2 different user inputs to filter the PivotTable by 2 values (the same as selecting two from the filter drop-down).?





Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'A2 through A5 is touched
If Intersect(Target, Range("A2:A5")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("Tab that contains PivotTable").PivotTables("PivotTable1")
Set Field = pt.PivotFields("FilterColumnName")
NewCat = Worksheets("Tab that contains PivotTable").Range("A3").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub

 

[EhhMikey]

Hi Jerry,

Again, I greatly appreciate your persistence and work on this!!

Per your instructions I ran the list and received the following result: "Table has 4 row(s) (Incl. header) and 1 column(s)" which aligns with the table I have created.

-Michael

 

[EhhMikey]

Hi Jerry,

I was testing different variations of the code today, I adjusted the pvf.VisibleItemsList = vSaveVisibleItemsList to vSaveVisibleItemsList = pvf.VisibleItemsList and instead of returning the 1004 “Application-defined or Object-defined error" it returned : "No matching items found.".

I'm not sure if that is helpful or solves the 1004 error, but it's now returning the message box created in your code.

Regards,
Michael

 

[Jerry Sullivan]

Hi Michael,

No worries. We'll track down the problem. The first test was to validate tblVisibleItemsList, and that looks okay.

This test will validate the PivotTable and it's relevant parts.

Sub Test2()
 Dim pvt As PivotTable
 Dim pvf As PivotField
 Dim sTypeName As String
 Dim vSaveVisibleItemsList As Variant

 On Error Resume Next
 Set pvt = ActiveSheet.PivotTables("PivotTable1")
 Set pvf = pvt.PivotFields("[Table5].[Name].[Name]")
 sTypeName = TypeName(pvf.VisibleItemsList)
 vSaveVisibleItemsList = pvf.VisibleItemsList
 On Error GoTo 0

 If pvt Is Nothing Then
   MsgBox "PivotTables named ""PivotTable1"" not found."
   
 ElseIf pvf Is Nothing Then
   MsgBox "PivotField named ""[Business Unit].[BusinessUnit by Channel].[PricingChannel]"" not found."
   
 ElseIf sTypeName <> "Variant()" Then
   MsgBox "VisibleItemsList is list invalid."

 Else
   MsgBox "VisibleItemsList has " & UBound(vSaveVisibleItemsList) & " item(s)"
 End If
End Sub

 

[EhhMikey]

Hi Jerry,

Per your test, the code erred on the following: PivotField named "[Business Unit].[BusinessUnit by Channel].[PricingChannel]"not found. I ran a filter macro on the pivot table to see what might be missing and received the following:

[/COLOR]Sub Macro1()'
' Macro1 Macro
'


'
    ActiveSheet.PivotTables("PivotTable1").CubeFields(17).EnableMultiplePageItems _
        = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Business Unit].[BusinessUnit by Channel].[PricingChannel]").VisibleItemsList _
        = Array("[Business Unit].[BusinessUnit by Channel].[PricingChannel].&[SBD]", _
        "[Business Unit].[BusinessUnit by Channel].[PricingChannel].&[Advantage]")
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Business Unit].[BusinessUnit by Channel].[BusinessUnit]").VisibleItemsList = _
        Array("")

End Sub[COLOR=#333333]

The part that looked missing to me was the CubeFields(17) I added this to our macro here: "Set pvt = ActiveSheet.PivotTables("PivotTable1").CubeFields(17)", reran the macro and received the following error message: "13 - Type mismatch"?

I'm not sure if that's helpful. But just an FYI.

BTW, how is San Diego? I lived in PB a few years after graduation. I absolutely love that place!!

 

[EhhMikey]

Hi Jerry,

I also tested

 ElseIf sTypeName <> "Variant()" Then   MsgBox "VisibleItemsList is list invalid."

and the Error message "VisibleItemsList is list invalid." came up as well.

 

[Jerry Sullivan]

Hi Michael,

I'm sorry. In the Test2 code I posted, I forgot to change the temporary field name that I was using in a test workbook "[Table5].[Name].[Name]", with the one that you are using.

Please try this test code instead...

Sub Test3()
 Dim pvt As PivotTable
 Dim pvf As PivotField
 Dim sTypeName As String
 Dim vSaveVisibleItemsList As Variant

 On Error Resume Next
 Set pvt = ActiveSheet.PivotTables("PivotTable1")
 Set pvf = pvt.PivotFields("[Business Unit].[BusinessUnit by Channel].[PricingChannel]")
 sTypeName = TypeName(pvf.VisibleItemsList)
 vSaveVisibleItemsList = pvf.VisibleItemsList
 On Error GoTo 0

 If pvt Is Nothing Then
   MsgBox "PivotTables named ""PivotTable1"" not found."
   
 ElseIf pvf Is Nothing Then
   MsgBox "PivotField named ""[Business Unit].[BusinessUnit by Channel].[PricingChannel]"" not found."
   
 ElseIf sTypeName <> "Variant()" Then
   MsgBox "VisibleItemsList is list invalid."

 Else
   MsgBox "VisibleItemsList has " & UBound(vSaveVisibleItemsList) & " item(s)"
 End If
End Sub

Yes, San Diego is a wonderful place to live!

 

[EhhMikey]

Hi,

The test provided the following message box: "VisibileItemsList has 2 item(s)". I then tried the code you provided and it works!!! The report filters on the items in the table!!

Thank you so much!! I will test and follow up should any additional questions arise. Again, I can't understate how much I appreciate your efforts on this.

Michael

 

[EhhMikey]

After testing it looks like I can create a table and the Cube Pivot Filter will set to what ever values are in that table! So now the application part and I hope these are simpler now that the initial part is over...

1. While testing I noticed if I have the pivot table on another sheet it will not call, can I set this to call ALL Pivot tables with the PivotField named ""[Business Unit].[BusinessUnit by Channel].[PricingChannel]"?
*I will need this as my pivots will be on one sheet and the data summary on another

2. Per the original request I need this to update two pivot tables with separate data sources, the second data source is just a data drop. Using a macro to set the second pivot table would look like this:

[/COLOR]Sub Macro2()'
' Macro2 Macro
'


'
    ActiveSheet.PivotTables("PivotTable2").PivotFields("FPA Channel"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("FPA Channel").CurrentPage _
        = "Advantage"

End Sub[COLOR=#333333]

Ideally, updating the "tblVisibleItemsList" table would update both pivots.

3. I would still like to select various options. When I add all of the options, i.e. Advantage, Retail, SBD and run the call, it selects all three, regardless of which ones I have selected from the drop down. My goal, is to have a summary page with a drop down (Range) where I can select these options and it will update both of the pivots (on separate sheets) then update the Sumifs I have connected to them. Will I need to create separate tables, i.e. "tblVisibleItemsList" then create drop down options that reference each table, i.e. "Advantage", "SBD" and "Total (Advantage + SBD)"? Is that possible?

I'm sorry for all of the questions... please let me know if there is anything I can clarify.

Michael

 

[EhhMikey]

Hi Jerry, I apologize for all of the emails, I'm working through this and hoping to solve some of these questions.

I was able to figure out Question 1, so we can scratch that. #2 I'm still not sure, I've tried various options to calling a basic pivot to "This Item" but none of them appear to be taking.

For number 3, I'm trying to figure out the range code you recommended here: OLAP VBA Filter but I'm not quite understanding which code from the calling example this would replace and where you would create your range.

 

[Jerry Sullivan]

Hi Michael,

Yes, you can use the one table list, "tblVisibleItemsList", to filter both types of Pivots (standard and OLAP-source). I'd recommend that the macro that you use to trigger the filtering, first determine whether the PivotTable is a standard or OLAP type. For OLAP, call the function you just sorted out. For standard Pivots, call the function I suggesting in Post #5 of this thread. The code you are showing in Post #38 is limited handling single-items in the Report (PageField) area of the PivotTable. The code in Post #5 can handle multiple items in any area of the PivotTable.

To determine the type of PivotTable source, you can test whether the PivotTable has the .MDX property. If so, it's OLAP.

Here's some example code you could integrate into your main macro...

Sub DeterminePivotType()
 Dim pvt As PivotTable
 Dim sTest As String

 For Each pvt In ActiveSheet.PivotTables
   Err.Clear
   On Error Resume Next
   sTest = pvt.MDX
   If (Err.Number = 0) Then
      MsgBox pvt.Name & " has MDX Property- is OLAP"
   Else
      MsgBox pvt.Name & " MDX Property returns Error-is standard Pivot"
   End If
   On Error GoTo 0
 Next pvt
End Sub