Looping through pivot items that have been filtered

Question

I am trying to copy the data values of a pivot table into another worksheet, but because I do filtering in the pivot table which "hides" a few of the row labels, when I loop through the pivot items of the pivot field in the row label, it loops a few more times than it actually should. To illustrate my problem here is the code I use.

Sub Prob()
Dim Ptable1 as PivotTable
Dim PField as PivotField
Dim PField2 as PivotField
Dim NumOfPItem as Long
Dim PItem as PivotItem
Dim aCell as Range

Set Ptable1 = ActiveSheet.PivotTables("PivotTable1")

With Ptable1.PivotFields("DataCol5")
    .CurrentPage = "12/2/2018"
End With

PField = Ptable1.PivotFields("DataCol1")
NumOfPItem = PField.PivotItems.Count

For each PItem in PField.PivotItems
   'Some code to get a cell reference in another worksheet

   'and then some code to copy the value from the pivot table to cells.
   aCell.Offset(0,1).Value = PField2.PivotItems("XX01").DataRange.Cells(PItem.Position,1)
Next PItem
End Sub

I just prepared something simple so that it would capture the main problem I am facing.

Unfiltered pivot table:

Field arrangements:

This is the pivot table after filtering it.

I tried looping through pivot items that were visible using the PivotItems.Visible property but the items are all visible hence looping through every pivot item there is. Leading to my next screenshot.

As seen, all items are selected so when looping through all "visible" pivot items from the pivot field, it would loop through it 5 times because there is 5 different items. However, in this example, after I do some filtering I end up with just 1 row label, so it should only be looping once for what is present in the pivot table. What am I doing wrong here?

Answer 1

You need to see if the pivotitem is visible. Also, I noticed you are using PI, this can be confusing as PI, so may be worth while changing that.

Here is an example

Sub x()

Dim p As PivotTable
Dim pf As PivotField
Dim pit As PivotItem

Set p = ActiveSheet.PivotTables(1)
Set pf = p.PivotFields("Name")

For Each pit In pf.PivotItems
    Debug.Print pit.Visible
Next pit

End Sub

Answer 2

Sub Solution()
Dim Ptable1 as PivotTable
Dim PField as PivotField
Dim PField2 as PivotField
Dim NumOfRows as Long
Dim PItem as PivotItem
Dim aCell as Range
Dim PFRng as Range
Dim i as long

Set Ptable1 = ActiveSheet.PivotTables("PivotTable1")

With Ptable1.PivotFields("DataCol5")
    .CurrentPage = "12/2/2018"
End With

PField = Ptable1.PivotFields("DataCol1")
PFRng = PField.DataRange
NumOfRows = PFRng.Rows.Count

For i = 1 to NumOfRows
   'Some code to get a cell reference in another worksheet

   'and then some code to copy the value from the pivot table to cells.
   aCell.Offset(0,1).Value = PField2.PivotItems("XX01").DataRange.Cells(i,1)
Next i
End Sub

I found a solution. Instead of looping through each pivot item found in the pivot field, I would just get the data range of the pivot field, look for how many rows is in the data range, and then loop through it starting from 1 to the last row. This way I would not have to worry about what is Visible. I can use "i" in place of the pivot item position to get values in the same row but in different fields.