none
filtering in Pivot Tables by loop RRS feed

  • Question

  • Hello,

    i have a code that works fine, but i want in the loop that it filters me the pivot-table step by step over the field "Nummer" - how do i have to adapt this code - thank you.

    Martin

    Sub test()

    Dim pt As PivotTable

        Dim pf As PivotField
        Dim pi As PivotItem
        Set pt = ActiveSheet.PivotTables("tblTest")
        ' change field as needed
        Set pf = pt.PivotFields("Nummer")

        For Each pi In pf.PivotItems
            MsgBox pi.Name
            
        Next pi
    End Sub

    Friday, April 13, 2018 1:07 PM

All replies

  • Not sure exact requirement. Below will copy pivottable to Z column for each item of Nummer.

    Sub test()
    
        Dim pt As PivotTable
    
        Dim pf As PivotField
        Dim pi As PivotItem
        Set pt = ActiveSheet.PivotTables("tblTest")
        Dim i As Long
        
        i = 1
        
        ' change field as needed
        
        Set pf = pt.PivotFields("Nummer")
    
        For Each pi In pf.PivotItems
            pf.ClearAllFilters
            pf.PivotFilters.Add Type:=xlCaptionEquals, Value1:=pi
            pt.TableRange2.Copy range("Z1").Offset(, i)
            i = i + 2
        Next pi
        
    End Sub
    


    Best Regards, Asadulla Javed

    Friday, April 13, 2018 2:02 PM
    Answerer
  • Hello Asadulla,

    Thank you very much the script works fine with a "normal" pivot table - but if the source of the pivot table is a datamodel in powerpivot i always get the error 1004 - now i have found a workaround with the following script - it works fine but has a small "error" - if all items are marked i get only one item back (item all) - is there a possibilty to get all items back (i have 124) - if i mark all and deselect one - i get 123 back - like wished.

    Any idea?

    Thank you Martin

     

    Sub SerienemailMarkierte()
    ' in english creating mass mail with marked items
    ' Makro2 Makro
    '
    Dim i2 As Integer
    Dim ar
     ar = ThisWorkbook.SlicerCaches("Datenschnitt_Agenturname").VisibleSlicerItemsList

    For i2 = 1 To UBound(ar)

          ActiveWorkbook.SlicerCaches("Datenschnitt_Agenturname").VisibleSlicerItemsList = Array(ar(i2))
          BereichAlsPDF

    'subroutine that creats a pdf and send it per mail

    Next

    End Sub


    • Edited by krugi Saturday, April 14, 2018 6:42 AM
    Saturday, April 14, 2018 6:41 AM
  • As per VBA help, VisibleItemList takes an array. If you want to see more items try something like

    Array(arr(i2),arr(i2+1)) instead of Array(arr(i2))

    It seems you want to see All Items except Array(arr(i2)). There is a property called HiddenItemsList.

    Probably that can help you


    Best Regards, Asadulla Javed

    Saturday, April 14, 2018 8:03 AM
    Answerer
  • Hello,

    thank you for your answer - no that is not my problem - if i mark all items - i delete the filtering - then i get only one item in the line of the loop     : ar(1) : "[DA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV].[Agenturname].[All]" : Variant/String instead of 124 (the pivot table has 124 different items - and i need them all in my loop)

    Any other idea?

    Thank you!

    Martin

    Saturday, April 14, 2018 6:04 PM
  • Say we have 5 items..a,b,c,d,e all marked in FilterBox -- What you want to be displayed ?

    Say we have 5 items..a,b,c,d,e marked in filterbox is a and d, unmarked is b,c,e -- What you want to be displayed ?



    Best Regards, Asadulla Javed

    Sunday, April 15, 2018 7:55 AM
    Answerer
  • Hello Asadualla,

    i think you have misunderstood my Problem.

    this Loop works perfect for all possibilties - but there is one error - if i mark all items in the slicer - this Loop ist only executed one time(because the array only has one field filled in the arrayh-and it should 124 times with the different items)

    this is the only field in the array - but i want to have all 124 items in the array

    [DA_HPFB_AgtnVDStuttgart_AlleAgtn_GVHV].[Agenturname].[All]

    For i2 = 1 To UBound(ar)

          ActiveWorkbook.SlicerCaches("Datenschnitt_Agenturname").VisibleSlicerItemsList = Array(ar(i2))
          BereichAlsPDF

    'subroutine that creats a pdf and send it per mail

    Next

    Have I explained it now correctly - thank you very much for your help

    Martin

    Sunday, April 15, 2018 4:09 PM
  • Hi krugi,

    You had mentioned that," if i mark all items in the slicer - this Loop ist only executed one time(because the array only has one field filled in the arrayh-and it should 124 times with the different items)"

    I can see that you are using "VisibleSlicerItemsList" in your code.

    Which returns or sets the list of MDX unique names for members at all levels of the hierarchy where manual filtering is applied.

    You can try to use "VisibleSlicerItems "which returns a SlicerItems collection that contains the collection of all the visible items in the specified slicer cache.

    Reference:

    SlicerCache Members (Excel)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 16, 2018 6:55 AM
    Moderator
  • thank you very much for your help, but like in the help is explained

    The  VisibleSlicerItems property is only applicable for slicers that are based on PivotTables based on workbook ranges or lists ( SlicerCache . SourceType = xlDatabase ). Attempting to access the VisibleSlicerItems property for slicers that are connected to an OLAP data source ( SlicerCache . OLAP = True ) generates a run-time error.

    I have a complex datamodell in powerpivot - therefore i get the error.

    Another idea?

    Thank you

    Martin

    Monday, April 16, 2018 5:09 PM
  • Hi krugi,

    You had mentioned that,"I have a complex datamodell in powerpivot - therefore i get the error."

    We don't have much idea regarding PowerPivot.

    Microsoft consider PowerPivot as a individual product which have it's own MSDN Support forum.

    You can also try to post the question there.

    So that you get the opinions of community members who have deep understanding regarding PowerPivot.

    MSDN PowerPivot Forum

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 18, 2018 7:52 AM
    Moderator