Loop and filter through each pivotfield item

[teatimecrumpet]

Hi,

I've got 56 pivot items in a pivotfield named "ID". What I'm trying to do is go through each pivot item and copy the first row of data under the pivotROWfield to another sheet. So basically only showing one of the pivotfields each time and copying the row I want to another sheet.



I could go through each pivotfield and manually change each other item to false and startin gover but that would be more than 56 times 56 lines of code. Anyway to do this with an IF statiment and/or with an array?



Thsi bad code does not work:

Set PT = Sheets("Full-Pivot").PivotTables("fullpivot")

For i = 1 To PT.PivotFields("ID").PivotItems.Count
Sheets("Full-Pivot").PivotTables("fullpivot").ManualUpdate = True
'PT.PivotFields("ID").PivotItems(i).Visible = True
If PT.PivotFields("ID").PivotItems(i).Value <> PT.PivotFields("ID").PivotItems(i).Value Then
PT.PivotFields("ID").PivotItems(i).Visible = False
'PT.PivotFields("ID").PivotItems.Visible = False
Range("A7").Copy Destination:=Sheets("sheet3").Range("A1048576").End(xlUp).Offset(1, 0)
End If
Next i

ANY help appreciated! Thanks!

 

[Jerry Sullivan]

Hi teatimecrumpet,

You might be able to achieve the same end result more directly with an approach that would have the pivottable display all 56 values in a contiguous range which could be copied in one step.

Please clarify:
1. What area of the PivotTable is the PivotField "ID" placed? (Report Filter, Column Label or Row Label)
2. Is the item in Cell A7 a Row Label or a DataField Value?

If a more direct approach isn't possible, I'd be glad to help with some code that could do the filter and copy process you describe without the need for 56 x 56 lines of code.

 

[teatimecrumpet]

Hi JS,

Thanks for the Reply!

• The PivotField "ID" is a PageField same as a Report Filter I guess
• The values I'm looking to grab is a Rowfield and the datafields. There are 5 data fields because it is a time series. Sorry my code had just A7 but I was just trying to grab that first to test.

So, ID is like a different flavor of girl scout cookies. And then the rowfield is the girl scout and her sales over the week is the data field. I'd like to grab the top selling girl scout for each cookie flavor and the post that in a new summary sheet.

Thanks!

 

[Jerry Sullivan]

Here's some code that you can try. You'll need to change "Sum of Sales1" to match the name of your "Top Sales" field.

Sub TopScouts()
    Dim i As Long
    Dim rTopRow As Range
    Application.ScreenUpdating = False
          
    With Sheets("Full-Pivot").PivotTables("fullpivot")
        Set rTopRow = Intersect(.DataBodyRange.Resize(1).EntireRow, .TableRange1)
        .ManualUpdate = False
        With .PivotFields("ID")
            .ClearAllFilters
            '--Sort by Sales field
             .Parent.PivotFields("Name").AutoSort xlDescending, "Sum of Sales1"
            For i = 1 To .PivotItems.Count
                .CurrentPage = .PivotItems(i).Value
                rTopRow.Copy _
                    Destination:=Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2)
            Next i
            .ClearAllFilters
        End With
    End With
    Application.ScreenUpdating = True
End Sub

You might want to modify this to place the ID value associated with each row on Sheet3.

As an alternative, you could make a pivottable on Sheet3 that has ID in the RowFields, and is filtered to show just the Top Sales for each ID. That would display the same information and it could be refreshed without the need for VBA or copying.

 

[teatimecrumpet]

Hi JS,

Worked perfectly! I didn't know about the "intersect" or "parent" parts of your code. It all just looked really slick. Very Impressed.


Regarding the suggestion about using a pivot table. I might actually just go that route. But I noticed that it looks at the sum of the whole row instead (in my case the total of Monday to Friday) instead of being the top just for Friday. For my particular report I think this is ok. Just wanted to let any others viewing this to know the difference.

Again thanks for the code it'll definitely come in handy later!

BTW if someone is building a pivot in VBA I recorded this that will allow you to just display the top 1 (or any other number you dictate in the filter):

ActiveSheet.PivotTables("Name_of_yourPivottable").PivotFields("NameoftheTop"). _
AutoShow xlAutomatic, xlTop, 1, "Sum of VALUE"

 

[Jerry Sullivan]

Glad to hear that worked and nice of you to post the xlTop code for others that might find this thread.

FYI, .Parent in this context is just a way to reference the PivotTable from within the .With block instead of having to repeat the entire reference.

.Parent.PivotFields("Name").AutoSort xlDescending, "Sum of Sales1"

This reference, gets the entire top row of the PivotTable.

Set rTopRow = Intersect(.DataBodyRange.Resize(1).EntireRow, .TableRange1)

This is more dynamic than using something like Range("A7:F7") since it will still work if you move your PivotTable on the sheet or add more columns.