VBA for Pivot to filter on cell value not working

S

shansakhi

Active Member
Joined
Apr 5, 2008
Messages
292
Office Version
  1. 365
Platform
  1. Windows
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,
 
Sort by date Sort by votes
A Power Pivot (OLAP Pivot) works a little differently.
See if the below works for you.
PS: I just found using pvt for a PivotField too confusing and changed it to pf

VBA Code: 
Sub OAL_OLAP_Pivot_Filter()
    Dim filtvalues As Variant, pitm As Variant, aItm As Variant
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim strFltr As String, arrFltr As Variant
    Dim aItmFlts As String
  
    With Sheets("Pivot")
        Set pt = .PivotTables("PT1")
        filtvalues = .Range("A1:A2").Value
    End With

    Set pf = pt.PivotFields("[DDS].[Merged].[Merged]")
    pf.ClearAllFilters
 
    For Each pitm In pf.PivotItems
        For Each aItm In filtvalues
            aItmFlts = "[" & aItm & "]"
            If InStr(1, pitm.Name, Trim(aItmFlts), vbTextCompare) > 0 Then
            strFltr = strFltr & "|" & WorksheetFunction.Substitute(pitm.Name, "[Merged]", "&" & aItmFlts, 2)
            Exit For
            End If
        Next aItm
    Next pitm

    If strFltr <> "" Then
        arrFltr = Split(Right(strFltr, Len(strFltr) - 1), "|")
        pf.VisibleItemsList = arrFltr
    Else
        MsgBox "None of the values exist"
    End If
End Sub
 
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.
Alex Blakenburg said:
A Power Pivot (OLAP Pivot) works a little differently.
See if the below works for you.
PS: I just found using pvt for a PivotField too confusing and changed it to pf

VBA Code: 
Sub OAL_OLAP_Pivot_Filter()
    Dim filtvalues As Variant, pitm As Variant, aItm As Variant
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim strFltr As String, arrFltr As Variant
    Dim aItmFlts As String
 
    With Sheets("Pivot")
        Set pt = .PivotTables("PT1")
        filtvalues = .Range("A1:A2").Value
    End With

    Set pf = pt.PivotFields("[DDS].[Merged].[Merged]")
    pf.ClearAllFilters
 
    For Each pitm In pf.PivotItems
        For Each aItm In filtvalues
            aItmFlts = "[" & aItm & "]"
            If InStr(1, pitm.Name, Trim(aItmFlts), vbTextCompare) > 0 Then
            strFltr = strFltr & "|" & WorksheetFunction.Substitute(pitm.Name, "[Merged]", "&" & aItmFlts, 2)
            Exit For
            End If
        Next aItm
    Next pitm

    If strFltr <> "" Then
        arrFltr = Split(Right(strFltr, Len(strFltr) - 1), "|")
        pf.VisibleItemsList = arrFltr
    Else
        MsgBox "None of the values exist"
    End If
End Sub
Click to expand...
Thank you so much it is working as desired for Merged pivot field.
But when I tried to change the Pivot field from Merged to other field name it is not working. It is showing "None of values available" even though values are available.
 
Upvote 0
shansakhi said:
Thank you so much it is working as desired for Merged pivot field.
But when I tried to change the Pivot field from Merged to other field name it is not working. It is showing "None of values available" even though values are available.
Click to expand...
Did you change the field name reference in both places ?
Rich (BB code): 
    Set pf = pt.PivotFields("[DDS].[Merged].[Merged]")
........
strFltr = strFltr & "|" & WorksheetFunction.Substitute(pitm.Name, "[Merged]", "&" & aItmFlts, 2)
 
Upvote 0
Ye
Alex Blakenburg said:
Did you change the field name reference in both places ?
Rich (BB code): 
    Set pf = pt.PivotFields("[DDS].[Merged].[Merged]")
........
strFltr = strFltr & "|" & WorksheetFunction.Substitute(pitm.Name, "[Merged]", "&" & aItmFlts, 2)
Click to expand...
Yes I did. I changed highlighted to another piovtfields.
 
Upvote 0
I have logged off for the night. Record a macro applying filters to that field.
Check the field references in the recorded macro matches your code.
Check the filter items match what you have in your list A1:A2

If that doesn’t find it show us
- the recorded macro
- your code
- your filter list (A1:A2)
 
Upvote 0
W
Alex Blakenburg said:
I have logged off for the night. Record a macro applying filters to that field.
Check the field references in the recorded macro matches your code.
Check the filter items match what you have in your list A1:A2

If that doesn’t find it show us
- the recorded macro
- your code
- your filter list (A1:A2)
Click to expand...
I noticed that if field is in "Rows" area of pivot then it works but the macro gives a "None of the values exist" if run on "Filters" area of pivot for same field.
 
Upvote 0
shansakhi said:
W

I noticed that if field is in "Rows" area of pivot then it works but the macro gives a "None of the values exist" if run on "Filters" area of pivot for same field.
Click to expand...
Unfortunately it works differently if you use a Page Filter. There is most likely a better way but give this a try:

VBA Code: 
Sub OAL_OLAP_Pivot_Filter_PageFltr()
    Dim filtvalues As Variant, aItm As Variant
    Dim pt As PivotTable
    Dim PF As PivotField
    Dim tmpFltr As String, strFltr As String, arrFltr As Variant
    Dim aItmFlts As String
   
    With Sheets("Pivot")
        Set pt = .PivotTables("PivotTable1")
        filtvalues = .Range("A1:A2").Value
    End With

    Set PF = pt.PivotFields("[DDS].[Merged].[Merged]")
    PF.ClearAllFilters

    For Each aItm In filtvalues
        aItmFlts = "[" & aItm & "]"
        On Error Resume Next
            tmpFltr = WorksheetFunction.Substitute(PF.Name, "[Merged]", "&" & aItmFlts, 2)
            PF.VisibleItemsList = Array(tmpFltr)
            If Err = 0 Then strFltr = strFltr & "|" & WorksheetFunction.Substitute(PF.Name, "[Merged]", "&" & aItmFlts, 2)
        On Error GoTo 0
    Next aItm

    If strFltr <> "" Then
        arrFltr = Split(Right(strFltr, Len(strFltr) - 1), "|")
        PF.VisibleItemsList = arrFltr
    Else
        MsgBox "None of the values exist"
    End If
End Sub
 
Upvote 0
Solution
Alex Blakenburg said:
Unfortunately it works differently if you use a Page Filter. There is most likely a better way but give this a try:

VBA Code: 
Sub OAL_OLAP_Pivot_Filter_PageFltr()
    Dim filtvalues As Variant, aItm As Variant
    Dim pt As PivotTable
    Dim PF As PivotField
    Dim tmpFltr As String, strFltr As String, arrFltr As Variant
    Dim aItmFlts As String
  
    With Sheets("Pivot")
        Set pt = .PivotTables("PivotTable1")
        filtvalues = .Range("A1:A2").Value
    End With

    Set PF = pt.PivotFields("[DDS].[Merged].[Merged]")
    PF.ClearAllFilters

    For Each aItm In filtvalues
        aItmFlts = "[" & aItm & "]"
        On Error Resume Next
            tmpFltr = WorksheetFunction.Substitute(PF.Name, "[Merged]", "&" & aItmFlts, 2)
            PF.VisibleItemsList = Array(tmpFltr)
            If Err = 0 Then strFltr = strFltr & "|" & WorksheetFunction.Substitute(PF.Name, "[Merged]", "&" & aItmFlts, 2)
        On Error GoTo 0
    Next aItm

    If strFltr <> "" Then
        arrFltr = Split(Right(strFltr, Len(strFltr) - 1), "|")
        PF.VisibleItemsList = arrFltr
    Else
        MsgBox "None of the values exist"
    End If
End Sub
Click to expand...
Apology for late reply.
The code is working as desired. Thank you so much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Filtering pivot table on dynamic range of data in cells
Replies
0
Views
511
Viper147
V
S
  • Question Question
Simplified VBA code to generate faster output
Replies
1
Views
380
shansakhi
S
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
272
Jette3173
J
A
  • Question Question
Pivot Table VBA Help
Replies
0
Views
607
ashley1984
A
T
  • Question Question
Clear FIlters of Pivot Table after Cell Value Changes
Replies
4
Views
493
Alex Blakenburg
A

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

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