-
OLAP pivot table vba filtering using VisibleItemsList
I have an OLAP pivot table. The following code works:
Sub MyCode()
Dim p as PivotTable
Set p = Worksheets("WorksheetName").PivotTables("PivotName")
Set x = p.PivotFields("[COUNTRY].[STATE].[ZIP]")
x.ClearAllFilters
x.CurrentPageName = "[COUNTRY].[STATE].&[12345]"
End Sub
For various reasons I need to use VisibleItemsList instead.
I tried below but I get an application or object defined error message.
What am I doing wrong? I don't understand using vba on these tables much.
Sub MyCode()
Dim p as PivotTable
Set p = Worksheets("WorksheetName").PivotTables("PivotName")
Set x = p.PivotFields("[COUNTRY].[STATE].[ZIP]")
x.ClearAllFilters
x.VisibleItemsList = Array("[COUNTRY].[STATE].&[12345]")
End Sub
-
I think what may have solved it is the following line:
x.cubefield.enablemultipleitems = true
Before I try to assign the visible items list.
By recording a macro, I saw that it was using
Cubefield(205).enablemultipleitems = true
I guess my field is number 205? And x.cubefield seemed to work when I didn't know the number.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules