Copy/Paste into Pivot Filter VBA

[ADeYoung]

I am trying to run a macro on a few items(24) in a large pivot table(6000). How can I run through a list, one at a time and then run another macro?

Here is what I have:

Sheets("Random Demand").Select
Range("O1").Select
ActiveCell.FormulaR1C1 = "D12549.256"
Sheets("Item Lookup").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("ITEM_NUMBER").CurrentPage _
= "D12549.256"
'Run other code here, then loop back and choose the next item on the list

The problem is the ActiveCell.FormulaR1C1 = "D12549.256", the range if cells with the items I need to use is Range("O1:O24"), that is what I want to use since the data will change and needs to be dynamic. any ideas?

 

[Jerry Sullivan]

Hi ADeYoung,

You could try the code below.

Sub Step_Through_PageFilters()
    Dim c As Range
    Dim PT As PivotTable
    Set PT = Sheets("Item Lookup").PivotTables("PivotTable1")

    
    For Each c In Sheets("Random Demand").Range("O1:O24")
        With PT.PivotFields("ITEM_NUMBER")
            .ClearAllFilters
            On Error Resume Next
            .CurrentPage = c.Text
            On Error GoTo 0
            If .CurrentPage.Value = c.Text Then
                

                [COLOR="#008080"]'Run other code here....[/COLOR]
               

                
            Else
                MsgBox c.Text & " pagefield item not found."
            End If
        End With
    Next c
End Sub

It's more efficient to just reference the objects directly as shown in this code instead of using "Select" and "ActiveCell".

 

[ADeYoung]

Thanks! That worked great.