• 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.

Select next Item in SlicerCaches for pivot table

mdfreeman

mdfreeman

New Member
Hey!

I am looking for a more efficient way to have my VBA roll through a list of items in a SlicerCache.

Current wisdom of the internet below results in repeated resetting of the filtered SlicerItems looping over and over again to get to each item. It is very inefficient when your list of items is long.

Code: 
Dim sI As SlicerItem, sI2 As SlicerItem, sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches("Slicer_site")
With sC
    For Each sI In sC.SlicerItems
        sC.ClearManualFilter
        For Each sI2 In sC.SlicerItems
            If sI.Name = sI2.Name Then sI2.Selected = True Else: sI2.Selected = False
        Next

     ''Insert your code to do what you want with the filtered pivot table
     ''Copy to a new tab or whatever you want
    
   Next
End With

I know I can count the number of items in the SlicerCaches with the code below.

Code: 
ActiveWorkbook.SlicerCaches(1).VisibleSlicerItems.Count

It would be great is there was way to select and item based on the position in the list of items in the SlicerCaches. With that you could roll through each value from what is in position 1 to the total value of the items in the list.

Anyone know if something like the index option exists?

Mark
 
If you ever iterate over PivotItems, set the PivotTable's .ManualUpdate to TRUE while you make your changes, and set it back to FALSE afterwards to avoid the PivotTable refreshing after each and every change. This will radically speed your code up.

If filtering on just one item, put hat field into your PivotTable as a PageField, and then just set the PageField value to the one thing you want. BAM! Done.


See my answer at https://stackoverflow.com/questions/49886464/speed-up-slicer-looping-in-vba/49887942#49887942 and also https://stackoverflow.com/questions/46026481/how-to-update-slicer-cache-with-vba/46109489#46109489
 
In addition to what Jeffrey wrote, you could use OLAP data model to build your pivot table and slicers (if Excel 2010 and up).

OLAP based slicer has a property ".VisibleSlicerItemsList", which isn't present for traditional slicers.

This is array of visible items (i.e. selected items) in slicer and it can filter for multiple items in one shot.

Have a read of thread below where this was used.
https://chandoo.org/forum/threads/c...n-a-range-of-cell-with-vba.37294/#post-224151
 
Hey @jeffreyweir : I like you idea.

Yes! Thank you. Using PageFields to cycling through my choices is a better option than that Slicer approach since I don't have to start each new loop by clearing the filters on the Slicer to get to the next item to filter on.

One downside: There are instances where the next PageField item returns no information in the pivot table because the date is blank (other filters applied to get to the data I want to view).

Options
(1.)I could set the VBA to move on quickly to the next value in the PageFields when the data output ISBLANK.

(2.) It would be great if PageFields could be restricted to only those visible items at the beginning (similar to the Slicer when you go into the properties).

(3.) If I know the items I want to review / loop through in the PageFields so I could setup a list of values to filter the PageFields value on. I suspect hints to the VBA for that solution are somewhere here on your website post: http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/
 
Last edited:
Hey @Chihiro:

I am not familiar with setting up an OLAP data model in Excel. My data source is not currently an OLAP model. My Google search are not coming up with useful information. What should I be searching on?

VisibleSlicerItemsList: Am I achieving the same results when I have adjusted the Slicer Settings to show "Hide items with no data"?


upload_2018-4-18_8-31-59.png
 
I am closer to the code I think will do the trick without a dictionary of items I want to filter on. I like the loop to stop at each item that has data in the pivot table, from there I'll add code to work with that data before looping.

The problem. I am running into Run-time error "5". Invalid procedure call or argument after the loop has gone through 8 times through blank items. Nothing is different data for the 8th PageFields item, the pivot data at that point is still blank.

Error is on this line:
Code: 
pf.CurrentPage = pi.Value

Suggestions on why I am getting the error message on this line?


The complete code I am running is:
Code: 
Sub Pivot_PageFields_Looping()
''Source: https://www.ozgrid.com/forum/forum/help-forums/excel-general/
''95768-pivot-table-report-filters-cycle-through-all

Dim pt As PivotTable, pi As PivotItem, pf As PivotField
Dim lLoop As Long
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PageFields("PO Number")
For Each pi In pf.PivotItems
    pf.CurrentPage = pi.Value
        If IsEmpty(Range("B14")) = False Then  ''check for data in pt
            MsgBox "1. There is data."
            MsgBox "2. Run macro on data."
            MsgBox "3. Move to the next PageFields item."
        End If
    lLoop = lLoop + 1
     
Next pi

End Sub
 
VisibleSlicerItemsList: Am I achieving the same results when I have adjusted the Slicer Settings to show "Hide items with no data"?
Click to expand...

No. VisibleSlicerItemsList is read/write property. Where you can set visible items to only items that you are interested in one shot, instead of looping.

As for loading to data model... if using Excel 2013 or later.
Google "PowerQuery load to data model" and you will find plenty of tutorials.

If using Excel 2010, search for "Excel 2010, PowerPivot add-in", as it's difficult to load data from PowerQuery into data model in Excel 2010 (also, unsupported).

As for CurrentPage issue, upload sample workbook. Without it, it's very difficult to help.
 
Thanks for taking a looking / spreadsheet attached.

I had to strip down the data to allow for the upload so the macro now breaks on the 1st loop with the same error.

Macro is Pivot_PageFields_Looping
 

Attachments

  • 2018-04-12-Clear_Old_Pos_v10_recovery_stripped_down_v3.xlsb
    786.7 KB · Views: 5
Excellent! Thank you! Works like a charm! Simple and easy

So what is the "pi.RecordCount >0" loop change?

Digging around I found a little more information about PivotItems with zero record count - This page was the best: Why Excel 2003 keeps PivotItems with zero record count after refresh?

I found on MrExcel another macro with a few more lines that also works well. Link to that post.

For those interested now or in the future here is the final code that @NARAYANK991 provided.

Code: 
Sub Pivot_PageFields_Looping()
'
'....................https://www.ozgrid.com/forum/forum/help-forums/excel-general/95768-pivot-table-report-filters-cycle-through-all
'
    Dim pt As PivotTable, pi As PivotItem, pf As PivotField
    Dim lLoop As Long
    Set pt = ActiveSheet.PivotTables(1)
    Set pf = pt.PageFields("PO Number")
    For Each pi In pf.PivotItems
        'pi.Value = pi.Value
        'ActiveSheet.PivotTables("PivotTable1").PageFields("PO Number").CurrentPage = pi.Value
        If pi.RecordCount > 0 Then
           pf.CurrentPage = pi.Value
           If IsEmpty(Range("B14")) = False Then
              MsgBox "1. This item would be one to do a tab on." ' Sheet1.PrintOut
              MsgBox "2. Run the work on the information."
              MsgBox "3. Move to the next item for working on."
           Else
              ''MsgBox "PageFields pivot data is empty"
           End If
           lLoop = lLoop + 1
           ''MsgBox lLoop
        End If
    Next pi
End Sub
 
You must log in or register to reply here.
Back
Top