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

  • 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?
    Friday, January 30, 2015 5:57 PM

Answers

  • 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

    Wednesday, February 4, 2015 12:04 AM

All replies

  • 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

    Monday, February 2, 2015 11:22 AM
  • 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?
    Tuesday, February 3, 2015 6:19 PM
  • 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

    Wednesday, February 4, 2015 12:04 AM
  • Thank you for your help. That worked perfectly!
    Wednesday, February 4, 2015 5:39 PM
  • 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.

    Wednesday, February 4, 2015 6:35 PM