How to loop through unique values in the pivot table filter field?

Question

I want to loop through individual values (sampling stations) in the filter field of a pivot table to copy and paste the report for each station to a new new tab without having to open select each station manually. How can I do this?

Answer 1

I might have been missing something, but don't the items that are listed in the row fields already unique?

Maybe you just need to loop through them, right?

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 2

Sorry, I'm not very good with the terminology. I have a pivot table with station ID's in the filter field. I need to copy and paste the report for each station ID into a new tab. There are thousands of stations, so I'd like to write a macro to do this automatically. The only piece I'm missing is code to loop through and select each station ID. Does this make more sense?

Answer 3

Sub pLoopThroughPivotFields()
    Dim oItem As Excel.PivotItem
    Dim oField As Excel.PivotField
    
    Set oField = ThisWorkbook.Worksheets("Sheet1").PivotTables("Pivot Table name").PivotFields("field name")
    For Each oItem In oField.PivotItems
        oField.CurrentPage = oItem.Name
        
        '
        'Put your copy code here
        '
        
        DoEvents
    Next oItem
End Sub

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 4

Thank you for your help. That worked perfectly!

Answer 5

No code is required. On the pivot table tab of Excel under the 'pivot table name' is a dropdown called 'options'.

Click that and choose 'show report filter pages'.

BOOM, everything in the filter gets its own tab.

It's pretty amazing.