G10
02-12-2020, 12:29 AM
Hi All,
I need some help regarding VBA to Export pivot table to individual PDF based on each value in the filter field.
Below is the screenshot of my pivot table:
25969
I'm getting the following message when i'm running the VBA: "microsoft visual basic error 400"
VBA 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("Summary")
Set PT = wksSource.PivotTables("PivotTable1")
Set pf = PT.PivotFields("Department_Allocation")
If pf.Orientation <> xlPageField Then
MsgBox "There's no 'Department_Allocation' field in the Report Filter. Try again!", vbExclamation
Exit Sub
End If
strPath = "C:\EXPORTDIRECTORY"
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 & "_Code.pdf"
Next pi
.ClearAllFilters
End With
End Sub
I need some help regarding VBA to Export pivot table to individual PDF based on each value in the filter field.
Below is the screenshot of my pivot table:
25969
I'm getting the following message when i'm running the VBA: "microsoft visual basic error 400"
VBA 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("Summary")
Set PT = wksSource.PivotTables("PivotTable1")
Set pf = PT.PivotFields("Department_Allocation")
If pf.Orientation <> xlPageField Then
MsgBox "There's no 'Department_Allocation' field in the Report Filter. Try again!", vbExclamation
Exit Sub
End If
strPath = "C:\EXPORTDIRECTORY"
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 & "_Code.pdf"
Next pi
.ClearAllFilters
End With
End Sub