Loop through pivot table

[skatman]

Hello i need loop through all "accounts" item in pivot table, and select one by one and save to pdf file the account statement for each account.

I have this code

Sub pivot_tables()

Dim p As PivotTable
Dim pf As PivotField
Dim pit As PivotItem
Dim pit2 As PivotItem
Dim saveLoc As String
Dim nome As String

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

saveLoc = "C:\test\"

ActiveSheet.PivotTables("PivotTable1").RefreshTable
For Each pit In pf.PivotItems
    pf.PivotItems(pf.PivotItems.Count - 1).Visible = True
    
    For Each pit2 In pf.PivotItems
        If pit2 = pit Then
        pit2.Visible = True
        Else
        pit2.Visible = False
        End If
        Next pit2
    nome = pit
    'Debug.Print pit.Visible
    'Debug.Print pit.Value
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLoc & nome
   
Next pit

End Sub

But i allways have error in the last account, i know pivot table need at least one item selected, is any way to solve this problem? i already try some tips i was reading but without any success.

 

[Georgiboy]

It depends on how you want it to look but another option may be to move Accounts into the filter section of the PT and use somthing like the below:

Sub pivot_tables()
    Dim pf As PivotField
    Dim pi As PivotItem
    
    With ActiveSheet.PivotTables("PivotTable1")
        .PivotFields("Account").ClearAllFilters
        Set pf = .PivotFields("Account")
        For Each pi In pf.PivotItems
            .PivotFields("Account").CurrentPage = pi.SourceName
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\test\" & pi.SourceName & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next
    End With
End Sub