Hi,
I am trying to iterate over all cells of a specific column in a pivot table, to fetch values of filtered data only. However, it returns all cells in the pivot table column, including the ones that are filtered out. The Pivot Table sits in another sheet than the one I am working with, hence the Worksheet_Activate() function.
The Debug.Print shows me that ALL cells in the column have .Visible = True (thus including the ones that are filtered out). I have tried to use pf.PivotItems rather than pf.VisibleItems as well with no luck.
Any ideas?
I am trying to iterate over all cells of a specific column in a pivot table, to fetch values of filtered data only. However, it returns all cells in the pivot table column, including the ones that are filtered out. The Pivot Table sits in another sheet than the one I am working with, hence the Worksheet_Activate() function.
Code:
Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Unprotect
ws.Range("Q7:Q30").ClearContents
Dim counter As Integer
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
counter = 0
Set pt = ThisWorkbook.Sheets("Pivot").PivotTables("pivotTable")
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
Set pf = pt.PivotFields("StoreName")
For Each pi In pf.VisibleItems
If pi.Visible = True Then
ws.Cells(7 + counter, "Q").Value = pi.Name
Debug.Print pi.Name, pi.Visible
End If
counter = counter + 1
Next pi
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub
The Debug.Print shows me that ALL cells in the column have .Visible = True (thus including the ones that are filtered out). I have tried to use pf.PivotItems rather than pf.VisibleItems as well with no luck.
Any ideas?
Last edited: