VBA to loop through and save Individual PDFs from a pivot table

[Saintshay]

Hi

I really hope someone can guide me please

I have a pivot table. There are over 700 individual records in my table.

When I filter on the records in my first row one by one the A9 will always state the individual record

My code below will save the records as a PDF however I have to filter and run my macro for each one.

Can someone please show me how to make it run for all records

Thanks in Advance

Sub Save_PDF()

Dim saveLocation As String
saveLocation = "C:\Users\SAINYSHAY\Desktop\PRINTS\PDF\" & Sheets("PRINTS").Range("A9").Value

Dim targetPivotTable As PivotTable
Set targetPivotTable = Sheets("PRINTS").PivotTables("PivotTable1")

Dim targetExportRange As Range
Set targetExportRange = targetPivotTable.TableRange2
targetExportRange.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=saveLocation, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

End Sub

 

[DanteAmor]

Hi and welcome to MrExcel board!

Change "NAME" in this line to the name of the field to filter. Set targetField = targetPivotTable.PivotFields("NAME")

Try this:

Sub Save_PDF()
  Dim saveLocation As String, itm As Variant
  Dim targetPivotTable As PivotTable
  Dim targetField As PivotField
 
  Set targetPivotTable = Sheets("PRINTS").PivotTables("PivotTable1")
  Set targetField = targetPivotTable.PivotFields("NAME")              '<---- Field name to filter
 
  For Each itm In targetField.PivotItems
    targetField.CurrentPage = Trim(itm)
    saveLocation = "C:\Users\SAINYSHAY\Desktop\PRINTS\PDF\" & Sheets("PRINTS").Range("A9").Value
    targetPivotTable.TableRange2.ExportAsFixedFormat xlTypePDF, saveLocation, 0, True, False, OpenAfterPublish:=False
  Next
  targetField.ClearAllFilters
End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

 

[Saintshay]

Hi and welcome to MrExcel board!

Change "NAME" in this line to the name of the field to filter. Set targetField = targetPivotTable.PivotFields("NAME")

Try this:

Sub Save_PDF()
  Dim saveLocation As String, itm As Variant
  Dim targetPivotTable As PivotTable
  Dim targetField As PivotField
 
  Set targetPivotTable = Sheets("PRINTS").PivotTables("PivotTable1")
  Set targetField = targetPivotTable.PivotFields("NAME")              '<---- Field name to filter
 
  For Each itm In targetField.PivotItems
    targetField.CurrentPage = Trim(itm)
    saveLocation = "C:\Users\SAINYSHAY\Desktop\PRINTS\PDF\" & Sheets("PRINTS").Range("A9").Value
    targetPivotTable.TableRange2.ExportAsFixedFormat xlTypePDF, saveLocation, 0, True, False, OpenAfterPublish:=False
  Next
  targetField.ClearAllFilters
End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

Hi DanteAmor


Thank you for this

Its debugging at

targetField.CurrentPage = Trim(itm)

When I highlight over it its states

Targetfield.current ...=<unable to get the current page property of the pivot

 

[DanteAmor]

You could activate the macro recorder, perform a filtering process, go back to stop the recorder. The generated code, you copy and paste it here.
Or you can share your workbook to check how your pivot table is doing.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.