Hi!
I'm using this code to filter a pivot table based on ItemId's in named range "Include_this":
So if an ItemID from pivot table is found in named range"Include_this" then it visible in a pivot table, otherwise it is not visible.
Today when I have created a new excel file with a new pivot table and the same "Include_this" range as before, I got an error when running the code. The code runs as usual, but then, when it comes to a certain ItemID, it stops. It shows
I have no idea what is wrong. As I've said, I'm using this code a lot and haven't had any trouble so far. Does somebody know what could be wrong?
I'm using this code to filter a pivot table based on ItemId's in named range "Include_this":
VBA Code:
Sub Filter_Pivot()
Dim PI As PivotItem
Dim i As Integer
With Worksheets("Data_chart").PivotTables("Data_ch1").PivotFields("ItemID")
.ClearAllFilters 'počisti filtre
For Each PI In .PivotItems
PI.Visible = WorksheetFunction.CountIf(Range("Include_this"), PI.Name) > 0
Next PI
End With
End Sub
So if an ItemID from pivot table is found in named range"Include_this" then it visible in a pivot table, otherwise it is not visible.
Today when I have created a new excel file with a new pivot table and the same "Include_this" range as before, I got an error when running the code. The code runs as usual, but then, when it comes to a certain ItemID, it stops. It shows
Error '1004': Unable to set the Visible property of the PivotItem class
Debugging shows there is something wrong with this line
VBA Code:
PI.Visible = WorksheetFunction.CountIf(Range("Include_this"), PI.Name) > 0
I have no idea what is wrong. As I've said, I'm using this code a lot and haven't had any trouble so far. Does somebody know what could be wrong?