• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Naming exported Pivot Table PDF Individual Summaries

2robertc

New Member
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 <<<
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:
Back
Top