Menu
0

How to use Pivot Tables in excel VBA? #Refresh #Filters #Locate


Please see the following code for using the pivottables.

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
ActiveWorkbook.RefreshAll

'Refresh a specific Pivot table
ActiveSheet.PivotTables("MyPivot").PivotCache.Refresh

Clearing Filters

ActiveSheet.PivotTables("MyPivot").PivotFields("ItemHeading").ClearAllFilters

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 )

0 views0 comments