Hello,
I'm in a bit of a sticky situation; I have a pivot table compiled using power query/dax and I'm now needing to print this out.
The problem is I need to:
- 1. Print this out by criteria/filter onto (preferrably) a pdf
- 2. Each filter to be printed onto a separate page
- 3. Incorporate the pivot table updating with new/changing data
I believe VBA is how to do this and I've copy/pasted several code through google searching, but none seem to work, or I get an error of "Compile error: Expected: identifier". I am new to VBA so this could be as simple as me not classing something properly.
An example of a script that I've tried is below.
Thank you in advance for any help!
---------------------------------------------------------------------
Sub ()
'prints a copy of pivot table for
'each item in page field
'assumes one page field exists
Application.ScreenUpdating = False
DirectoryLocation = ActiveWorkbook.Path
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.EmployeeName).CurrentPage = pi.EmployeeName
Columns("B:B").ColumnWidth = 8.14
Name = DirectoryLocation & "\" & Range("B2").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Next
Next pf
Application.ScreenUpdating = True
----------------------------------------------------
Bookmarks