VBA for Pivot to filter on cell value not working

[shansakhi]

Hello All,
I am trying to apply below VBA code to a pivot to filter based on cell range.
But I am getting error 400.

Sub OAL()
Dim filtvalues As Variant
Dim i As Integer, j As Integer
Dim pvt As PivotField
Dim pitm As PivotItem
filtvalues = Sheets("Pivot").Range("A1:A2")
Set pvt = Sheets("Pivot").PivotTables("PT1").PivotFields("[DDS].[Merged].[Merged]")
pvt.ClearAllFilters
For i = 1 To pvt.PivotItems.Count
Set pitm = pvt.PivotItems(i)
pitm.Visible = False
For j = 1 To UBound(filtvalues, 1) - LBound(filtvalues, 1) + 1
If pitm.Name = filtvalues(j, 1) Then
pitm.Visible = True
Exit For
End If
Next j
Next i
End Sub

I recorded the actions to see the system result of multiple selection.
How do I add below highlighted in filtvalues = Sheets("Pivot").Range("A1:A2") of above code.

Range("B7").Select
ActiveSheet.PivotTables("PT1").PivotFields("[DDS].[Merged].[Merged]"). _
VisibleItemsList = Array("[DDS].[Merged].&[BOM-LHR]")
ActiveSheet.PivotTables("PT1").PivotFields("[DDS].[Merged].[Merged]"). _
VisibleItemsList = Array("[DDS].[Merged].&[BOM-LHR]", _
"[DDS].[Merged].&[LHR-BOM]")
Range("B7").Select
End Sub



Regards,

 

[DanteAmor]

Try this:

Sub OAL()
  Dim filtvalues As Variant, pitm As Variant, aItm As Variant
  Dim pt As PivotTable
  Dim pvt As PivotField
  Dim bExists As Boolean
  Dim n As Long
  
  With Sheets("Pivot")
    Set pt = .PivotTables("PT1")
    filtvalues = .Range("A1:A2").Value
  End With
  
  Set pvt = pt.PivotFields("DDS")
  pvt.ClearAllFilters
  
  For Each pitm In pvt.PivotItems
    bExists = False
    For Each aItm In filtvalues
      If LCase(Trim(aItm)) = LCase(pitm.Value) Then
        bExists = True
        Exit For
      End If
    Next
    If bExists = False Then
      n = n + 1
      If n < pvt.PivotItems.Count Then
        pitm.Visible = False
      Else
        pvt.ClearAllFilters
        MsgBox "None of the values exist"
     End If
    End If
  Next
End Sub

Note Code Tag:

In future please use code tags when posting code.​

How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.​

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[shansakhi]

Thank you for the reply. But same error of 400
I have loaded the data in table called DDS and then creating a pivot table based on this.


Sub OAL()
Dim filtvalues As Variant, pitm As Variant, aItm As Variant
Dim pt As PivotTable
Dim pvt As PivotField
Dim bExists As Boolean
Dim n As Long

With Sheets("Pivot")
Set pt = .PivotTables("PT1")
filtvalues = .Range("A1:A2").Value
End With

Set pvt = pt.PivotFields("DDS") 'DDS is Table name and merged is Pivotfield name
pvt.ClearAllFilters

For Each pitm In pvt.PivotItems
bExists = False
For Each aItm In filtvalues
If LCase(Trim(aItm)) = LCase(pitm.Value) Then
bExists = True
Exit For
End If
Next
If bExists = False Then
n = n + 1
If n < pvt.PivotItems.Count Then
pitm.Visible = False
Else
pvt.ClearAllFilters
MsgBox "None of the values exist"
End If
End If
Next
End Sub

 

[shansakhi]

Try this:

Sub OAL()
  Dim filtvalues As Variant, pitm As Variant, aItm As Variant
  Dim pt As PivotTable
  Dim pvt As PivotField
  Dim bExists As Boolean
  Dim n As Long
 
  With Sheets("Pivot")
    Set pt = .PivotTables("PT1")
    filtvalues = .Range("A1:A2").Value
  End With
 
  Set pvt = pt.PivotFields("DDS")
  pvt.ClearAllFilters
 
  For Each pitm In pvt.PivotItems
    bExists = False
    For Each aItm In filtvalues
      If LCase(Trim(aItm)) = LCase(pitm.Value) Then
        bExists = True
        Exit For
      End If
    Next
    If bExists = False Then
      n = n + 1
      If n < pvt.PivotItems.Count Then
        pitm.Visible = False
      Else
        pvt.ClearAllFilters
        MsgBox "None of the values exist"
     End If
    End If
  Next
End Sub

Note Code Tag:

In future please use code tags when posting code.​

How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.​

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

I tried changing the

pt.PivotFields("DDS") to pt.PivotFields("[DDS].[merged].[merged]")

It clear the filters but do not take the filvalues range A1:A2


Sub OAL1()
Dim filtvalues As Variant, pitm As Variant, aItm As Variant
Dim pt As PivotTable
Dim pvt As PivotField
Dim bExists As Boolean
Dim n As Long

With Sheets("Pivot")
Set pt = .PivotTables("PT1")
filtvalues = .Range("A1:A2").Value
End With

Set pvt = pt.PivotFields("[DDS].[merged].[merged]")
pvt.ClearAllFilters

For Each pitm In pvt.PivotItems
bExists = False
For Each aItm In filtvalues
If LCase(Trim(aItm)) = LCase(pitm.Value) Then
bExists = True
Exit For
End If
Next
If bExists = False Then
n = n + 1
If n < pvt.PivotItems.Count Then
pitm.Visible = False
Else
pvt.ClearAllFilters
MsgBox "None of the values exist"
End If
End If
Next
End Sub

 

[Peter_SSs]

@shansakhi
Did you miss Dante's note?

Note Code Tag:
In future please use code tags when posting code.How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

 

[shansakhi]

@shansakhi
Did you miss Dante's note?

Thank you. Yes .. I missed this.

 

[shansakhi]

Sub OAL1()
  Dim filtvalues As Variant, pitm As Variant, aItm As Variant
  Dim pt As PivotTable
  Dim pvt As PivotField
  Dim bExists As Boolean
  Dim n As Long
 
  With Sheets("Pivot")
    Set pt = .PivotTables("PT1")
    filtvalues = .Range("A1:A2").Value
  End With
 
  Set pvt = pt.PivotFields("[DDS].[merged].[merged]")
  pvt.ClearAllFilters
 
  For Each pitm In pvt.PivotItems
    bExists = False
    For Each aItm In filtvalues
      If LCase(Trim(aItm)) = LCase(pitm.Value) Then
        bExists = True
        Exit For
      End If
    Next
    If bExists = False Then
      n = n + 1
      If n < pvt.PivotItems.Count Then
        pitm.Visible = False
      Else
        pvt.ClearAllFilters
        MsgBox "None of the values exist"
     End If
    End If
  Next
End Sub

 

[shansakhi]

I am trying to add code as suggested by DanteAmor, but unable to do so.
AM I doing something different.

 

[Peter_SSs]

AM I doing something different.

Yes, you put both the code tags after the code. Read the instructions carefully again. You should end up with one tag before the code and one after, like this.
[CODE=vba]
Your code goes here
[/CODE]

I have fixed it for you this time.

 

[shansakhi]

THa

Yes, you put both the code tags after the code. Read the instructions carefully again. You should end up with one tag before the code and one after, like this.
[CODE=vba]
Your code goes here
[/CODE]

I have fixed it for you this time.

Thank you.