• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Refer to PivotTable in DataModel

paulshlapa

New Member
Hi everyone,
I have a code, which works great with to filter out values in a pivot table, which is located on a different sheet, but i can not get it to work within a DataModel environment (Runtime 1004)
Code:
Sub Test_Filter()
    Dim PI As PivotItem
    With Worksheets("PIVOT ALL").PivotTables("PivotTable1").PivotFields("ID")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("Filter"), PI.Name) > 0
        Next PI
    End With
End Sub
Any suggestions?

Thank you very much
 
I modified it a little bit, but it still does not apply to the filter (it works if one attaches it to slicer, but it's not what is needed) giving me a 1004 mistake on the last two lines
Code:
Sub Test_Filter()
     

Dim ar1
    Dim strConst As String
strConst = "[All].[ID].&["
    ReDim ar1(1 To Cells(Rows.Count, "E").End(xlUp).Row - 1)
   
    For I = 1 To UBound(ar1)
        ar1(I) = strConst & Cells(I + 1, "E").Value2 & "]"
    Next

Sheets("PIVOT ALL").PivotTables("PivotTable1").PivotFields( _
    "[All].[ID].[ID]").VisibleItemsList = Array(ar1)

End Sub

Any suggestions?

Thank you
 
Basically i am able to create a dynamic array either as a string or a variant on Sheet "Cars", but cannot apply it to the "Pivottable1" on sheet "PIVOT ALL" to the field "ID"
 
Back
Top