Cycle Through Pivot Filter and Print to PDF

[DPhilip]

Hi Guys,

I have a VBA code that cycles through my pivot filter and prints the file to PDF. It filters the vendor number and I print a summary for each vendor. The problem is one vendor has 4 different summaries and each pivot is on a different worksheet. When I run the below Macro, it saves 4 files in 4 separate folders. I wanted to merge them so I send one PDF with 4 pages vs. 4 PDFS with single pages.

I really appreciate any help I can get.

Thanks in advance.
Dennis

Option Explicit
Sub test()
    Dim strPath As String
    Dim wksSource As Worksheet
    Dim PT As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
   
    Set wksSource = Worksheets("AP Pivot")
   
    Set PT = wksSource.PivotTables("PivotTable2")
   
    Set pf = PT.PivotFields("Vendor #")
   
    If pf.Orientation <> xlPageField Then
        MsgBox "There's no 'Vendor #' field in the Report Filter.  Try again!", vbExclamation
        Exit Sub
    End If
   
    strPath = "G:\"
   
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
   
    ActiveWorkbook.ShowPivotTableFieldList = False
   
    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

 

[progeb]

The option to save the entire workbook as opposed to the active worksheet, which is available on the Save As menu, doesn't seem to be translated in the macro I generated. You may need to copy all your pivot tables to one worksheet first, then export.

 

[DPhilip]

Can you post the Macro you are talking about in your reply Progeb?

 

[DPhilip]

Anyone know how I can loop through a list and change the filter based on that list and print packet to PDF?

 

[241marina241]

Hi
I am new to using VBA and I almost nothing about it. I was able to take the code in the first post and change it for my pivot table. The only bit it doesn’t like is the line
.CurrentPage = pi.Name
When I take this line out it runs but each PDF have the different vendor numbers but they all have the same data, the posit hasn’t been filtered.
Could anyone please advise why this is?