'Creating Individual PDFs from a Pivot Table using a macro
I am currently working on writing a macro to go through the Pivot Items in my pivot table and PDF them all as their respective Item names. The code below is stuck on line 26 '.CurrentPage = pi.Name', with the pop up stating: 'Run-time error '1004': Unable to set the CurrentPage Property of the PivotField Class'.
If I remove this line, it does cycle through the the items and save them as pdfs, but the item itself has not been selected by the macro so the pdf displays whatever is on the screen at that momeent.
I am new to VBA so may well be missing something simple, or may be well off! So any help would be greatly appreciated.
Sub PDF_CGT()
Dim strPath As String
Dim wksSource As Worksheet
Dim PT As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set wksSource = Worksheets("Sheet3")
Set PT = wksSource.PivotTables("PivotTable1")
Set pf = PT.PivotFields("PC")
strPath = "Directory"
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 & ".pdf"
Next pi
.ClearAllFilters
End With
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
