0

My goal is to use VBA to dynamically hide all items in a PivotTable field list except for an array of items stored in a separate table. The catch is it's an OLAP cube so I have to use the Cube field method. Also I am only aware of a way to hide specific items. What I want to do is hide everything except specific items.

I am getting an

Application Defined or Object Related Error

on the line with .HiddenItemsList

I know I am way off on syntax but quite lost at this point. 

Public Sub FilterRd()
Dim arCC As Variant
Dim pfCostCenterCode As PivotField
Dim PF As PivotField
Dim PI As PivotItem

arCC = Worksheets("CCs").ListObjects("tblCCs").DataBodyRange.Value
arCC = Application.Transpose(arCC)

Set pfCostCenterCode = Worksheets("RDA").PivotTables("PTccs").PivotFields("[DCC].[CCC].[CCC]")
a = Filter_PivotField(pfCostCenterCode, arrCC)

End Sub

Private Function Filter_PivotField(pvtField As PivotField, varItemList As Variant)
    Dim i As Long
   ' On Error GoTo ErrorHandler:
    Application.ScreenUpdating = False

    With pvtField
        .ClearAllFilters
        .CubeField.IncludeNewItemsInFilter = True
    End With

    For i = LBound(varItemList) + 1 To UBound(varItemList)
        pvtField.HiddenItemsList = Array("[DCC].[CCC].&" & varItemList(i) & "]")
    Next i

ErrorHandler:
    MsgBox "Error while trying to process item: " & varItemList(i)
End Function
SanomaJean
  • 181
  • 1
  • 2
  • 14
  • 1
    It looks to me like you're missing the opening "[" for each item in varItemList. What happens if you amend your code to this: pvtField.HiddenItemsList = Array("[DCC].[CCC].&" & varItemList(i) & "]") – jeffreyweir Feb 18 '18 at 20:49
  • 1
    Also, you should be building an array and THEN feeding it to pvtField.HiddenItemsList but instead you are creating a .HiddenItemsList of just one item at a time, and then feeding it to pvtField.HiddenItemsList. You need to build the one array containing the things you want to remain visible, and then feed it to .HiddenItemsLIst – jeffreyweir Feb 18 '18 at 20:51
  • @jeffreyweir good eye with the bracket! But I need a reverse way instead of hidden items list... there is a huge list of possibilities that is ever changing and impossible to nail down. The user can only send me what they want to show and there's not really a way for me to get a comprehensive list in order to back out what should be hidden. – SanomaJean Feb 20 '18 at 22:56
  • I think you should check out my answer at https://stackoverflow.com/questions/48404166/pivot-table-filter-out-only-1-option/48409297#48409297 – jeffreyweir Feb 21 '18 at 03:51
  • @jeffreyweir that's very helpful thank you! – SanomaJean Feb 21 '18 at 19:40
  • Cool...will be good if you can mark that answer as helpful. So do you still need help to implement the same thing here? All that you want that is different from the other thread as far as I can tell is to feed in an array, whereas in my answer at the other thread the array is hard-coded. – jeffreyweir Feb 21 '18 at 22:04
  • @jeffreyweir I marked it! Nope, that's helpful thank you! – SanomaJean Feb 28 '18 at 16:04

0 Answers0