VBA - loop on pivot filter values, save to .pdf

[ABC_123]

Hi there

I need some help on a VBA loop

In PowerPivot, I've created a data model based pivot, which will be used to create .pdf reports for hundreds of items. I want to make a VBA loop so I won't have to filter and save to .pdf for each individual value in the filter.

The loop would be something like:

- filter on first filter value
- refresh pivot
- print pivot to .pdf
- filter on the second filter value
… and so on

Until now I have written the following code:

Sub PDF_Indivudual_Summaries()
Dim strPath As String
Dim wksSource As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set wksSource = Worksheets("Report")

Set pt = wksSource.PivotTables("PivotTable1")

Set pf = pt.PivotFields("Number")

If pf.Orientation <> xlPageField Then
MsgBox "There's no 'Personeelsnummer' field in the Report Filter. Try again!", vbExclamation
Exit Sub
End If

strPath = "C:\Users\User\Documents"

If Right(strPath, 1) <> "" Then strPath = strPath & ""

ActiveWorkbook.ShowPivotTableFieldList = False

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh

With pf
.ClearAllFilters
For Each pi In .PivotItems
.CurrentPage = pi.Name
wksSource.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & pi.Name & "_report.pdf"
Next pi
.ClearAllFilters
End With

End Sub

The problem I am facing is that when I run this code, on this line...

Set pf = pt.PivotFields("Number")

… a run-time error appears:

Run-time error '1004':

Unable to get the PivotField property of the PivotTable class

I don't understand why I'm getting this message. When i place my cursor in the report filter, the Active Field is exactly called "Number" as in VBA reference.

Please help me out

Thanks!

 

[ABC_123]

UPDATE: i've tested the VBA script on a simple pivot table and that worked perfectly fine thus there seems to be wrong with the field "Number". This field has been used in the PivotTable model as a primary key, may that be the reason why the script doesn't work?

 

[DarenTC]

Hi - Did you solve your issue? I am facing same issue with a pivot table created from a Table and as a Data Model. It doesn't recognise any Pivot Items. Even if I record a macro selecting one of the page items in the pivot and use this code as a test, it fails.