Excel 2016 VBA - Compare 2 PivotTables fields for matching values

inmh88 · Sep 3, 2018

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]

inmh88 · Sep 4, 2018

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

RoryA · Sep 4, 2018

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

inmh88 · Sep 4, 2018

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.

RoryA · Sep 4, 2018

Str2 is already an array so remove the Array() function from around it.

inmh88 · Sep 4, 2018

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!

RoryA · Sep 4, 2018

The function should already be doing that. What is the current code you have for bFieldItemExists?

inmh88 · Sep 4, 2018

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

RoryA · Sep 4, 2018

That isn't what I posted earlier.

I'm out this afternoon so probably won't be able to look again until tomorrow.

inmh88 · Sep 4, 2018

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.

inmh88 · Sep 4, 2018

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

Excel 2016 VBA - Compare 2 PivotTables fields for matching values

inmh88

New Member

inmh88

New Member

Excel Facts

RoryA

MrExcel MVP, Moderator

inmh88

New Member

RoryA

MrExcel MVP, Moderator

inmh88

New Member

RoryA

MrExcel MVP, Moderator

inmh88

New Member

RoryA

MrExcel MVP, Moderator

inmh88

New Member

inmh88

New Member

Similar threads

Forum statistics

Share this page

Excel 2016 VBA - Compare 2 PivotTables fields for matching values

New Member

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock