Select Power Pivot PT Item
Results 1 to 2 of 2

Thread: Select Power Pivot PT Item
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,399
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Select Power Pivot PT Item

    I am looping through row labels in a PT using the following (which doesn't feel efficient but it's the only way I can check each item value)
    Code:
    Set ptField = ptPivotTableList.PivotFields("[AD Expected].[Proper Name].[Proper Name]")
          
          For Each ptItem In ptField.PivotItems
             Set rngPerson = Range("FA_IndividualsStartBDM").Offset(1, 0)
             
             Do Until rngPerson = ""
             
                If InStr(ptItem.Name, rngPerson.Value) > 0 Then
                   strPerson = rngPerson
                   
                   Exit Do
                   Else
                   strPerson = ""
                End If
                Set rngPerson = rngPerson.Offset(1, 0)
             Loop
                
             If strPerson = "" Then
                Else
                If rngPerson.Offset(0, 2) = strSector And rngPerson.Offset(0, 3) = "N" Then
                   ptItem.Visible = True
                   Else
                   ptItem.Visible = False
                End If
             End If
          Next
    My problem is with the ptItem.Visible = part as I am getting a 1004 error.

    Again, I've not got a clue why this isn't working but I'm assuming it's because it using the Power Pivot to establish the PT.


    TIA

  2. #2
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,399
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select Power Pivot PT Item

    I thought the following would work but it's causing a run time error
    Code:
    Dim strArray As String
    
    strArray = ""
          Do Until rngPerson = ""
                If strArray = "" Then
                   strArray = Chr(34) & "[AD Expected].[Proper Name].&[" & rngPerson & "]" & Chr(34)
                   Else
                   strArray = strArray & ", " & Chr(34) & "[AD Expected].[Proper Name].&[" & rngPerson & "]" & Chr(34)
                End If
    
             Set rngPerson = rngPerson.Offset(1, 0)
          Loop"
    
    ptPivotTableList.PivotFields("[AD Expected].[Proper Name].[Proper Name]").VisibleItemsList = Array(strArray)
    Last edited by mikeymay; Aug 22nd, 2019 at 11:38 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •