I am looking to pdf each pivot table item (compound) based on a criteria (max(cell range) greater than 0
ignore the vendor stuff, this code is pulled from the internets.
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("Pivot_Table")
Set PT = wksSource.PivotTables("PivotTable1")
Set pf = PT.PivotFields("Compound")
If pf.Orientation <> xlPageField Then
MsgBox "There's no 'Vendor #' field in the Report Filter. Try again!", vbExclamation
Exit Sub
End If
strPath = "C:\Users\peastling\Documents\Kinchelo PDFS"
If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
ActiveWorkbook.ShowPivotTableFieldList = False
PT.PivotCache.Refresh
With pf
.ClearAllFilters
For Each pi In .PivotItems
If Range("D5").Calculate > 0 Then
.CurrentPage = pi.Name
wksSource.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & pi.Name & ".pdf"
End If
Next pi
.ClearAllFilters
End With
End Sub
The cell c5 contains the formula =MAX(B11:AQ90) (the maximum size of my pivot table) I think I am missing something, like update cell or calculate cell value, but with out the If Then statement, I print out all 200 tables and i only want the ones with data (max > 0)
Thanks in advance!
Bookmarks