Pivot table output to pdf with multiple page fields

[TheoMa]

Hi,

I have a pivot table with 3 page fields, PageF1, PageF2 and PageF3
Each has a number of values including (All)

I want to go through every combination of the pagefields and if the pivot table has actual values in the data region to output to a pdf file with a filename format: Pagef1 - PageF2 - PageF3 .pdf

The code goes through each page combination, but
I cant exclude page fields with (all)
i cant capture the names of the page fields of the combinations that have data in the data body range in a filename and use that filename to output the pivottable

I am using the following code that i found at http://www.contextures.com/xlPivot09.html

Your help is welcomed

Sub PrintPivotPages()
'prints a copy of pivot table for
'each item in page field

On Error Resume Next

Dim pt As PivotTable
Dim File As Variant 'somehow this would be used to define the filename need
Dim pf As PivotField
Dim pi As PivotItem
Dim RngToSum As Range

Set pt = ActiveSheet.PivotTables.Item(1)
Set RngToSum = Nothing

For Each pf In pt.PageFields

For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name

'this is the data range of the pivottable
Set RngToSum = pt.DataBodyRange
If Application.WorksheetFunction.Sum(RngToSum) <> 0 Then
ActiveSheet.PrintPreview 'use this for testing
​

'Specify acrobat file name and type
'This portion does not work, need help:
Set File = pi.value.pdf
'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= File, _
'Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
':=False, OpenAfterPublish:=True


End If
Next
Next pf
End Sub