Select Power Pivot PT Item

[mikeymay]

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)

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

 

[mikeymay]

I thought the following would work but it's causing a run time error

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)