I'm very new to VBA Macros.
The below code works great to include the "MD" in the exported PDF file name.
I want to include the "Prac" (This is a field name in my pivot table) before the "MD" in my exported PDF file name.
Ideally, I'd like to include the field "Prac" in my Pivot Table as a filter but it doesn't change when I start the macro and the MD loops around creating the PDFs. I can also include it as a row in my PDF if that's necessary to make the macro work.
Your assistance is much appreciated.
>>> use code - tags <<<
The below code works great to include the "MD" in the exported PDF file name.
I want to include the "Prac" (This is a field name in my pivot table) before the "MD" in my exported PDF file name.
Ideally, I'd like to include the field "Prac" in my Pivot Table as a filter but it doesn't change when I start the macro and the MD loops around creating the PDFs. I can also include it as a row in my PDF if that's necessary to make the macro work.
Your assistance is much appreciated.
>>> use code - tags <<<
Code:
Sub PDF_Indivudual_Summaries()
'In this case I have a pivot table called PivotTable1 on the worksheet called Summary, and I want to export a PDF file for each value in analyis_codes4 available in the pivot table.
'The files will in this case be saved in directory C:\EXPORTDIRECTORY\.
Dim strPath As String
Dim wksSource As Worksheet
Dim PT As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set wksSource = Worksheets("MD_DETAIL")
Set PT = wksSource.PivotTables("PivotTable1")
Set pf = PT.PivotFields("MD")
If pf.Orientation <> xlPageField Then
MsgBox "There's no 'analysis_codes4' 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 & "Scorecard_PriMed_FY24_DETAILS_" & pi.Name & ".pdf"
Next pi
.ClearAllFilters
End With
End Sub
Last edited by a moderator: