Home Printing Excel Pivot Filter Reports and Tables to PDF
Reply: 0

Printing Excel Pivot Filter Reports and Tables to PDF

Nathan Stvartak
1#
Nathan Stvartak Published in 2018-01-12 20:51:34Z

I create pivot reports very regularly and usually end up creating charts based on those graphs and need to print reports for different filter values in that Pivot Report. Currently, I'm working on a specific report and would like a VBA Macro to print all the filter options.

I found something similar to what I'm looking for @ http://www.contextures.com/xlPivot09.html#downlaod however, it's not working for me.

I'm also trying to make this dynamic so I can use this for other pivots down the road.

Sub PrintPivot()
'Used contextures.com as resource
'On Error Resume Next
Dim ws As Worksheet
Dim pt As pivottable
'Dim pf As PivotField
'Dim pi As PivotItem
Dim FileLocation As Variant
Dim pivottable As Variant

TableName = InputBox("What is the name of the Pivot Table")

Set ws = Sheets("Sheet1") 'Look @ Active Sheet
Set pt = ws.PivotTables.TableName '???
'Set pf = pt.PageFields '???

FileLocation = InputBox("Where are these PDF's going to be stored?") 'Set Location of PDF Files

'If PivotField Is Nothing Then Exit Sub
'MsgBox ("Past Exit")

pivotcount = ActiveSheet.PivotTables(TableName).FilterField("District Council").Count
MsgBox ("Total PDFs = " & pivotcount)


For Each PivotItem In PivotTables.PivotItems  'For each option in PivotItems Print
IMSeUsage.PivotFields("District Council").CurrentPage = PivotItem.Name ' "District Council" = Pivot Table Filter Value

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=FileLocation & "\" & PivotItem & ".pdf", _
Quality:=xlQualityMedium, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

Next PivotItem


MsgBox ("Complete")
End Sub

I have since corrected this code to do the following:

Sub PrintPivot()
'Used contextures.com as resource
Dim ws As Worksheet
Dim pt As pivottable
Dim FileLocation As Variant
Dim pivottable As Variant
Dim Rng As Variant
Dim item As Variant
On Error GoTo Errorcatch

TableName = InputBox("What is the name of the Pivot Table")
item = InputBox("What Pivot Field do you want changed?")
Rng = InputBox("What cell do you want to change the values for" & item & "?")

FileLocation = InputBox("Where are these PDF's going to be stored?") 'Set Location of PDF Files

For Each PivotItem In ActiveSheet.PivotTables(TableName).PivotFields(item).PivotItems  'For each option in PivotItems Print

Range(Rng).Value = PivotItem
ActiveSheet.PivotTables(TableName).RefreshTable
Application.Wait (Now + TimeValue("0:00:01"))

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=FileLocation & "\" & PivotItem & ".pdf", _
Quality:=xlQualityMedium, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

Next PivotItem

MsgBox ("Complete")
Exit Sub

Errorcatch:
MsgBox Err.Description
End Sub
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304222 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO