Consulting

Results 1 to 2 of 2

Thread: OLAP pivot table vba filtering using VisibleItemsList

  1. #1
    VBAX Newbie
    Joined
    Jul 2020
    Posts
    2
    Location

    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

  2. #2
    VBAX Newbie
    Joined
    Jul 2020
    Posts
    2
    Location
    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
  •