electriclunch
New Member
- Joined
- May 24, 2015
- Messages
- 3
Hi All,
I run a weekly report for my department that pulls from a pivot table. The macro I used filters on the name for each distributor and saves a pdf file of the data for each one in a separate folder. An issue I am encountering is that it will save a PDF for a distributor even if there is no data returned from the pivot. How can I stop my macro from creating a PDF when there is no data tied to a distributor?
Below is my current code:
Sub Macro1()
Dim strPath As String
Dim wksSource As Worksheet
Dim PT As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set wksSource = Worksheets("PivotSummaryTable")
Set PT = wksSource.PivotTables("PivotTable2")
Set pf = PT.PivotFields("Agent Name")
If pf.Orientation <> xlPageField Then
MsgBox "There's no 'Distributor' field in the Report Filter. Try again!", vbExclamation
Exit Sub
End If
strPath = "D:\Report\Distributors"
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 & ".pdf"
Next pi
.ClearAllFilters
End With
End Sub
Any help is greatly appreciated!!!
I run a weekly report for my department that pulls from a pivot table. The macro I used filters on the name for each distributor and saves a pdf file of the data for each one in a separate folder. An issue I am encountering is that it will save a PDF for a distributor even if there is no data returned from the pivot. How can I stop my macro from creating a PDF when there is no data tied to a distributor?
Below is my current code:
Sub Macro1()
Dim strPath As String
Dim wksSource As Worksheet
Dim PT As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set wksSource = Worksheets("PivotSummaryTable")
Set PT = wksSource.PivotTables("PivotTable2")
Set pf = PT.PivotFields("Agent Name")
If pf.Orientation <> xlPageField Then
MsgBox "There's no 'Distributor' field in the Report Filter. Try again!", vbExclamation
Exit Sub
End If
strPath = "D:\Report\Distributors"
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 & ".pdf"
Next pi
.ClearAllFilters
End With
End Sub
Any help is greatly appreciated!!!