PDA

View Full Version : OLAP pivot table vba filtering using VisibleItemsList



Dj4n4
07-24-2020, 09:14 AM
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

Dj4n4
07-24-2020, 10:57 AM
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.