deadlyliquidx
New Member
- Joined
- Feb 6, 2015
- Messages
- 27
Hi all,
I have never been confused in my life with VBA!! But first time for everything, if you guys get this, you would be gods amongst men.
I am trying to filter an OLAP cube pivot table (pivot table 5) to a range of cells ("store list") on the pivot field ( "[Location].[Location].[Store]").
This code works perfectly when doing it on a non-cube based local pivot but on the pivot table I get this error message "unable to set visible property of pivotitem class".
It recognizes the pivot, clears filters and everything but the problem is at Pi.visible, where it for some reason cannot set the pivot filter.
I would have provided a worksheet but it wouldent have mattered because you need to be connected to my OLAP to see the problem because this code works fine on pivots connected to local spreadsheets.
Please HELP!!
Thanks Guys
XOXO
I have never been confused in my life with VBA!! But first time for everything, if you guys get this, you would be gods amongst men.
I am trying to filter an OLAP cube pivot table (pivot table 5) to a range of cells ("store list") on the pivot field ( "[Location].[Location].[Store]").
Code:
Sub Test2()
Dim pi As PivotItem
With Worksheets("Sheet1").PivotTables("PivotTable5").PivotFields("[Location].[Location].[Store]")
.ClearAllFilters
For Each pi In .PivotItems
pi.Visible = WorksheetFunction.CountIf(ActiveSheet.Range("Storelist"), pi.Name) > 0
Next pi
End With
End Sub
This code works perfectly when doing it on a non-cube based local pivot but on the pivot table I get this error message "unable to set visible property of pivotitem class".
It recognizes the pivot, clears filters and everything but the problem is at Pi.visible, where it for some reason cannot set the pivot filter.
I would have provided a worksheet but it wouldent have mattered because you need to be connected to my OLAP to see the problem because this code works fine on pivots connected to local spreadsheets.
Please HELP!!
Thanks Guys
XOXO