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 )
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. https://www.pls-fix-thx.com/post/vba-print-or-save
Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba
Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python
If you have found this article or website helpful. Please show your support by visiting the shop below.
EPTU Machine ETPU Moulding…
EPTU Machine ETPU Moulding…
EPTU Machine ETPU Moulding…
EPTU Machine ETPU Moulding…
EPTU Machine ETPU Moulding…
EPS Machine EPS Block…
EPS Machine EPS Block…
EPS Machine EPS Block…
AEON MINING AEON MINING
AEON MINING AEON MINING
KSD Miner KSD Miner
KSD Miner KSD Miner
BCH Miner BCH Miner
BCH Miner BCH Miner