This Used to Work ...

Philip1957

Board Regular
Joined
Sep 30, 2014
Messages
174
Greetings,

I have this code to set pivot filters that used to work until about a month ago and it's driving me crazy.

VBA Code:
Private Sub SetFilter16()

Dim PI As PivotItem
Dim v As Variant
Dim r As Range
Dim pf As PivotField
    
With Worksheets("Pivot Filters")
        Set r = .Range(.Range("D2"), .Cells(.Rows.Count, "D").End(xlUp))
    End With
    
For Each PI In Worksheets("Pivots").PivotTables("Piv16_").PivotFields("Date Conc").PivotItems
        PI.Visible = True
    Next PI
    
For Each PI In Worksheets("Pivots").PivotTables("Piv16_").PivotFields("Date Conc").PivotItems
        v = Application.Match(PI.Name, r, False)
        If IsError(v) Then PI.Visible = False
    Next PI

End Sub

When it gets to
VBA Code:
If IsError(v) Then PI.Visible = False
I get an error saying "unable to set the Visible property of the Pivot Class.

What on earth changed?

Thanks in advance for any assistance.

~ Phil
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
1,057
Office Version
  1. 365
Platform
  1. Windows
You could give this a try

VBA Code:
Sub jec()
  r = Sheets("Pivot Filters").Range("D2", Sheets("Pivot Filters").Cells(Rows.Count, "D").End(xlUp))
  With ActiveSheet.PivotTables("Piv16_")
    .ClearAllFilters
      For Each it In .PivotFields("Date Conc").PivotItems
         v = Application.Match(Val(it.Name), r, 0)
         If IsError(v) Then it.Visible = False
      Next it
  End With
End Sub
 

Philip1957

Board Regular
Joined
Sep 30, 2014
Messages
174
Never mind.

Once of my external data sources got changed without my knowledge.
I fixed the query and now my code runs fine.
 

Forum statistics

Threads
1,176,698
Messages
5,904,493
Members
435,094
Latest member
mlh0308

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
Top