Excel 2016 VBA - Compare 2 PivotTables fields for matching values

I

inmh88

New Member
Joined
Sep 3, 2018
Messages
11
Hi please can someone help, Excel 2016 VBA PivotTable objects. I rarely develop in Excel VBA (as I'm a SQL Dev).

Overall Goal:
Compare a single column [P_ID] value list from PivotTable2 against PivotTable1 filter field, if they exist or not to enable filtering on those valid values in PivotTable1. Needs to compare against the entire list of [P_ID] in PivotTable1.

I have some Excel 2016 VBA code which I have adapted from a previous answer from a different internet source.

Logic is:
Gather data from PivotTable2 from the ComparisonTable dataset (all from a PowerPivot model), field [P_ID] list of values. Generate a test line as input into function to test for existence of field and value in PivotTable1 against the Mastertable dataset, if true add the line as valid if not skip the line. Finally filter PivotTable1 with the VALID P_ID values.


It works to a point until it gets to the bFieldItemExists function which generates an error:

Run-time error '1004' Unable to get the PivotItems property of the PivotField class
Can someone please correct the way of this not working?
Code: 
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Private[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheet_PivotTableUpdate[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#101094][FONT=inherit]ByVal[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Target [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] PivotTable[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MyArray [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    ar [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    x [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    y [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    str [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

MyArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ActiveSheet[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotTables[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"PivotTable2"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotFields[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"[ComparisonTable].[P_ID].[P_ID]"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]DataRange

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ar [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit] MyArray
    x [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"[MasterTable].[P_ID].&["[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ar [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"]"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ar [/FONT][/COLOR][COLOR=#303336][FONT=inherit]<>[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]""[/FONT][/COLOR][COLOR=#101094][FONT=inherit]And[/FONT][/COLOR][COLOR=#303336][FONT=inherit] bFieldItemExists[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]x[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] str [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]""[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            str [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"[MasterTable].[P_ID].&["[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ar [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"]"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Else[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            str [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] str [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]","[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"[MasterTable].[P_ID].&["[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ar [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"]"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ar


[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] str2[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    str2 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Split[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]str[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]","[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]EnableEvents [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ScreenUpdating [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

        ActiveSheet[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotTables[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"PivotTable1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotFields[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"[MasterTable].[P_ID].[P_ID]"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]VisibleItemsList [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Array[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]str2[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]EnableEvents [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ScreenUpdating [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit]


[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit] bFieldItemExists[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]strName [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Boolean[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] strTemp [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]' This line does not work!?[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
  strTemp [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ActiveSheet[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotTables[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"PivotTable1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotFields[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"[MasterTable].[P_ID].[P_ID]"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PivotItems[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]strName[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Err [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit] bFieldItemExists [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Else[/FONT][/COLOR][COLOR=#303336][FONT=inherit] bFieldItemExists [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR]</code>[COLOR=#242729][FONT=Arial]
[/FONT][/COLOR]

 
Sort by date Sort by votes
Hi, thanks for all your help really appreciate it! As I say I'm not a vb developer, SQL BI by trade, have to get this done within 2 days though...

I've revised the code, and I get another error when it hits the code line in the function :

' This line throws an error
If pf.CurrentPageName = "[MasterTable].[P_ID].&[" & strName & "]" = True Then ...



Microsoft Visual Basic
Run-time error 1004
Application-defined or object defined error


The [P_ID] in the PivotTable 1 is in a pivot drop-down filter, used to filter the pivot.

I do think we're getting closer to a solution!

Thanks!


Revised code:

Code: 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)


If Target = "PivotTable2" Then
    
    Dim MyArray As Variant, _
        ar      As Variant, _
        x       As String, _
        str     As Variant
        
    'On Error Resume Next
    Set MyArray = ActiveSheet.PivotTables("PivotTable2").PivotFields("[ComparisonTable].[P_ID].[P_ID]").DataRange


    For Each ar In MyArray
       ' x = "[MasterTable].[P_ID].&[" & ar & "]"
        x = ar.Value2
        If ar.Value2 <> "" And bFieldItemExists(x) = True Then
            If str = "" Then
                str = "[MasterTable].[P_ID].&[" & ar.Value2 & "]"
            Else
                str = str & "," & "[MasterTable].[P_ID].&[" & ar.Value2 & "]"


            End If
        End If


    Next


'-------------------------------------------------
' Apply Filterlist to target PivotTable
'-------------------------------------------------
    Dim str2() As String
    
        str2 = Split(str, ",")


        'Application.EnableEvents = False
        'Application.ScreenUpdating = False


            'On Error Resume Next
            ActiveSheet.PivotTables("PivotTable1").PivotFields("[MasterTable].[P_ID].[P_ID]").VisibleItemsList = Array(str2)
           


        'Application.EnableEvents = True
        'Application.ScreenUpdating = True


End If  'Endif Target


End Sub


Function bFieldItemExists(strName As String) As Boolean


    Dim strTemp As Variant, _
        pt      As PivotTable, _
        pf      As PivotField, _
        result  As Boolean


    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("[MasterTable].[P_ID].[P_ID]")
   
    'pf.ClearAllFilters
    
    ' This line throws an error
    If pf.CurrentPageName = "[MasterTable].[P_ID].&[" & strName & "]" = True Then
    
    result = 1
        Else
    result = 0
    End If
    
    If result = True Then
        bFieldItemExists = True
    Else
        bFieldItemExists = False
    End If
    
End Function
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You need to handle the error as before:

Code: 
pf.ClearAllFilters
On Error Resume Next
pf.CurrentPageName = "[MasterTable].[P_ID].&[" & strName & "]"
bFieldItemExists = err.number = 0
on error goto 0
 
Upvote 0
Hi, when I add that, I now get an error:

Run-time error 1004
Then item could not be found in the OLAP cube

From this line, when trying to apply the filter:

Code: 
ActiveSheet.PivotTables("PivotTable1").PivotFields("[MasterTable].[P_ID].[P_ID]").VisibleItemsList = Array(str2)

The previous function should have filtered out non-valid ID's. :confused:
 
Upvote 0
Str2 is already an array so remove the Array() function from around it.
 
Upvote 0
I get the Same error message
Run-time error 1004
Then item could not be found in the OLAP cube


Is there any way to re-write this

Code: 
ActiveSheet.PivotTables("PivotTable1").PivotFields("[MasterTable].[P_ID].[P_ID]").VisibleItemsList = str2


So that either it gets applied on a one-by-one basis rather than all in one go, before generating an error?

Or error trapping to see which value is doesn't like?

thanks!



 
Last edited:
Upvote 0
The function should already be doing that. What is the current code you have for bFieldItemExists?
 
Upvote 0
Code: 
Function bFieldItemExists(strName As String) As Boolean


    Dim strTemp As Variant, _
        pt      As PivotTable, _
        pf      As PivotField, _
        result  As Boolean


    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("[MasterTable].[P_ID].[P_ID]")
    
    On Error Resume Next


    If pf.CurrentPageName = ("[MasterTable].[P_ID].&[" & strName & "]" = True Then
   	result = 1
    Else
    	result = 0
    End If
 	On Error GoTo 0
   
    If result = True Then
        bFieldItemExists = True
    Else
        bFieldItemExists = False
    End If
        
End Function
 
Upvote 0
That isn't what I posted earlier.

I'm out this afternoon so probably won't be able to look again until tomorrow.
 
Upvote 0
Hi, sorry, I've updated it:
Code: 
Function bFieldItemExists(strName As String) As Boolean


    Dim strTemp As Variant, _
        pt      As PivotTable, _
        pf      As PivotField, _
        result  As Boolean


    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("[MasterTable].[P_ID].[P_ID]")


    
    'pf.ClearAllFilters
    On Error Resume Next
    pf.CurrentPageName = "[MasterTable].[P_ID].&[" & strName & "]"
    bFieldItemExists = Err.Number = 0
    On Error GoTo 0
End Function

What happens now is that no error comes up, but only the LAST value in the [P_ID] list is actually filtered on.
Its not stepping through each value and applying it.
 
Last edited:
Upvote 0
Hi, I've solved it, I noticed that in the [P_ID] Filter drop-down box on the active Excel sheet, the 'multiple-items' tick box was not selected when using this automated method. Not sure at which point it got switched off, but switching it back on and resetting the pivot filter back to default makes it all work.

The only 'issue' I have it that for 2,000 [P_ID]'s it takes an age for the routine to finish (~2 mins). At some times I may have thousands of [P_ID]'s

Can the code be made more efficient of faster?

Thanks again! :)

Final Code for review:

Code: 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)


If Target = "PivotTable2" Then
    
    Dim MyArray As Variant, _
        ar      As Variant, _
        x       As String, _
        str     As Variant
        
            
    'On Error Resume Next
    Set MyArray = ActiveSheet.PivotTables("PivotTable2").PivotFields("[ComparisonTable].[P_ID].[P_ID]").DataRange




    For Each ar In MyArray
       ' x = "[MasterTable].[P_ID].&[" & ar & "]"
        x = ar.Value2
        If ar.Value2 <> "" And bFieldItemExists(x) = True Then
            If str = "" Then
                str = "[MasterTable].[P_ID].&[" & ar.Value2 & "]"
            Else
                str = str & "," & "[MasterTable].[P_ID].&[" & ar.Value2 & "]"


            End If
        End If


    Next




'-------------------------------------------------
' Apply Filterlist to target PivotTable
'-------------------------------------------------
    Dim str2() As String
    
        str2 = Split(str, ",")


        Application.EnableEvents = False
        Application.ScreenUpdating = False


        On Error Resume Next
        
        ActiveSheet.PivotTables("PivotTable1").CubeFields(19).EnableMultiplePageItems = True
        ActiveSheet.PivotTables("PivotTable1").PivotFields("[MasterTable].[P_ID].[P_ID]").VisibleItemsList = Array("")
        ActiveSheet.PivotTables("PivotTable1").PivotFields("[MasterTable].[P_ID].[P_ID]").VisibleItemsList = str2
           
        Application.EnableEvents = True
        Application.ScreenUpdating = True


    Set MyArray = Nothing
    Set ar = Nothing
    'Set x = ""
    Set str = Nothing
    Set strTemp = Nothing
    Exit Sub


End If  'Endif Target


End Sub


Function bFieldItemExists(strName As String) As Boolean


    Dim strTemp As Variant, _
        pt      As PivotTable, _
        pf      As PivotField, _
        result  As Boolean


    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("[MasterTable].[P_ID].[P_ID]")


    'pf.ClearAllFilters
    On Error Resume Next
    
    pf.CurrentPageName = "[MasterTable].[P_ID].&[" & strName & "]"
    bFieldItemExists = Err.Number = 0
    pf.ClearAllFilters
    
    On Error GoTo 0


End Function
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Dynamically Update Slicer Selection based on pivot table filtering
Replies
0
Views
2K
novicev
N
J
  • Locked
  • Question Question
Excel vba - filter pivots
Replies
1
Views
509
Fluff
Fluff
J
  • Locked
  • Question Question
VBA Filter Pivot Tables on different sheets
Replies
1
Views
4K
Fluff
Fluff
J
  • Locked
  • Question Question
Pivot Table Filter Button - VBA
Replies
1
Views
548
Fluff
Fluff
J
  • Question Question
Filter Pivot Tables - VBA
Replies
2
Views
2K
joejs
J

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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