Loop through pivot table

skatman

New Member
Joined
Jan 11, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

VBA 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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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:
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top