top of page

How to: "vba print pivot table to pdf"

Please see the following code for using the pivottables.

vba pivot tables locate

Incorporating pivot tables in your VBA script can really speed up your scripts. Not the pivot tables in itself, but you are leveraging what is already so good about them when it comes to displaying an insanely reduced dataset.

For the most part I tend to create the pivot tables in excel as normal. At this point I have the basis set, and I mostly use the pivot refreshing options, filter settings and the pivot table location for determining my range I wish to loop through. For example I can have two pivot tables where I loop through the first one, and uses that one for the filter setting for the second pivot table. And from here usually copy the second pivot table for any subsequent process.

Refreshing a Pivot

'Refresh all Pivot tables

'Refresh a specific Pivot table

Clearing Filters


Add Filter for single select filter

ActiveSheet.PivotTables("MyPivot").PivotFields("ItemHeading").CurrentPage = YourFilter

Add Filter for multiselect filter

'To turn on multiselect option. 
ActiveSheet.PivotTables("MyPivot").PivotFields("ItemHeading").EnableMultiplePageItems = True

'Need to turn off the ones you don't want to show compared to previous filtersetting.
With ActiveSheet.PivotTables("MyPivot").PivotFields("ItemHeading")

     .PivotItems("Filter1").Visible = False
     .PivotItems("Filter2").Visible = False
     .PivotItems("Filter3").Visible = False

End With 

Pivot table location

'Setting Pvt as my pivot table.
Dim Pvt As PivotTable
Set Pvt = ActiveSheet.PivotTables("MyPivot")

'Setting Rng for my Pivot table range
Set Rng = Pvt.TableRange1

'Displays My Pivot table range
MsgBox (Rng.Address)

'Displays the address of my upper left corner, starting position for my pivot table.
Top_Left = Rng.Cells(1, 1).Address
MsgBox (Top_Left )

'Displays the address of my bottom left corner.
Bottom_Left = Rng.Cells(Rng.Rows.Count, 1).Address
MsgBox (Bottom_Left )

'Displays the address of my upper right corner.
Top_Right = Rng.Cells(1, Rng.Columns.Count).Address
MsgBox (Top_Right )

'Displays the address of my bottom right corner.
Bottom_Right = Rng.Cells(Rng.Rows.Count, Rng.Columns.Count).Address
MsgBox (Bottom_Right )​

Now that we know how to refresh and work with pivot tables we can simply use my other article to leverage the print or save command to either save as excel or pdf, or to simply just print out our pivot table result.

Learn more about VBA here for all my posts:

Learn more about Python here for all my posts:

If you have found this article or website helpful. Please show your support by visiting the shop below.

189 views0 comments


bottom of page