Can I use a Loop to change the filters in a Pivot Table from Power Pivot?

[bigck2]

Hello,

I am trying to use a VBA loop to adjust the filters on a Pivot Table built with Power Pivot to make and print a report for each business unit.

I have been trying to use the Macro recorder but I get an error message saying "Unable to set the CurrentPage property of the PivotField Class"


Here is the code I am using (again this mainly just modified from the Macro recorder):

Sub PropertiesLoop()




    Worksheets("Properties").Activate
    
    Dim LR As Long
    Dim PropertyName As String
    
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To LR
    
        PropertyName = Cells(i, 1).Value
        
        Worksheets("Revenue PT").Activate
        
        
        ActiveSheet.PivotTables("SS Revenue").PivotFields( _
        "[SameStores].[Project  Name].[Project  Name]").ClearAllFilters
        
        ActiveSheet.PivotTables("SS Revenue").PivotFields( _
        "[SameStores].[Project  Name].[Project  Name]").CurrentPage = _
        "[SameStores].[Project  Name].&[" & PropertyName & "]"
            
        
    Next i

End Sub

Please let me know if what I am wanting to do is possible?

Thanks,

Chris

 

[scottsen]

Ya, but it's tricky. We were also talking about it here: http://www.mrexcel.com/forum/power-...t-sequence-cubemember-cubevalue-formulas.html

 

[bigck2]

Hey Scottsen,

I'll have to look at your other thread in more detail.

In case anyone else reads this I just wanted to say that I had much better results using slicers than filters. Not sure why, but I was able to accomplish what I needed using some variation of this code:

     Sheets("Rev SS PT").Select
        
                    ActiveWorkbook.SlicerCaches("Slicer_Project__Name1").ClearAllFilters
                    ActiveWorkbook.SlicerCaches("Slicer_Project__Name1").VisibleSlicerItemsList = _
                    Array( _
                    "[SameStores].[Project  Name].&[" & PropertyName & "]")


                    Range("A1").Value = PropertyName