CODEWITHSUNDEEP
vbaexcel
Brad
Brad

Reputation: 1480

Iterating Pivot Table

I have code which iterates through every PivotItem in my pivot table. The code I displayed below is a more manual approach. My loop holds PivotItem's outside of what is being shown in the pivot table because there is a filter on the pivot table to limit things. So lets say I use .PivotItems("BMR") and it the code works fine as there are BMR PivotItems to display within the Pivot Table. However if I do .PivotItems("EHFG") it will error out because it isn't in the PivotTable as it is being filtered out.

How can I go about adjusting my code so that it won't break on non-existing pivot items? Perhaps skip the With statement if it doesn't exist? 

Option Explicit
Sub test()

    Dim PvtTbl As PivotTable
    Dim columnsDifference As Long
    Dim PvtFld As PivotField

    Set PvtTbl = Sheets("NIR Pivot").PivotTables("NIR_Pivot")
    Set PvtFld = PvtTbl.PivotFields("Prod. Code")

    PvtFld.PivotItems("EHFG").ShowDetail = True 'Show pivot item


    columnsDifference = PvtTbl.TableRange2.Columns.Count - PvtTbl.DataBodyRange.Columns.Count
    With PvtTbl.PivotFields("Prod. Code").PivotItems("EHFG").DataRange **ERRORS HERE
   '     Debug.Print .Offset(, -columnsDifference).Resize(.Rows.Count, .Columns.Count + columnsDifference).Address
        .Offset(, -columnsDifference).Resize(.Rows.Count, .Columns.Count + columnsDifference).Select
    End With

End Sub

Upvotes: 1

Views: 138

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Try the modified For Each loop code below:

Dim Pvtitm As PivotItem ' define pivot-item object

' loop thorugh all Pivot-items in Pivot Field "Prod. Code"
For Each Pvtitm In PvtFld.PivotItems
    ' check if current Pivot item is filtered out
    If Pvtitm.Visible = True Then
        With Pvtitm.DataRange
            'Debug.Print .Offset(, -columnsDifference).Resize(.Rows.Count, .Columns.Count + columnsDifference).Address
            .Offset(, -columnsDifference).Resize(.Rows.Count, .Columns.Count + columnsDifference).Select
        End With
    Else
        MsgBox "Pivot-Item " & Pvtitm.Name & " is filtered out", vbInformation
    End If

Next Pvtitm

Note: not sure why you would need to Select the Range ?

Upvotes: 1

Related Questions