VBA to Filter Pivot Table Field

SanjayGulatiMusafir

Board Regular
Joined
Sep 7, 2018
Messages
92
Hi Experts,
I may get wrong with some technical terms but try to Explain what challenge I'm going through after collecting some information from net and forming a VBA...

I have a sheet - "Bank".
It has got multiple Tables.
On one of the tables named "Banks" I created a Pivot Table "ExpAnalysis"

The fields I used are as follows -
Rows - "Dt" - Grouped by Year & Months
Columns - "D1"
Values - "Sum of Less"
Filter - "Acc"

The Pivot table was working fine as intended.
Now I got a thought - If I could create a VBA that helps me see what I want to see in Just one click

The aim -
Filter - "Acc", the Filter Field to all Values that contain "CC" in beginning
Filter - "Dt" Row Field to "Year" to '2021' or the 'Current Year'

Please help me identify where I'm going wrong and help it improve.

Thanks a lot

I started as below (Obviously with my novice hands I picked bits and pieces from net to get what I wanted)

VBA Code:
Sub GoExpAnalysis()
'
' GoExpAnalysis Macro
'

'
    Sheets("Bank").Select

    
    'To Calculate as I have kept my Excel to Manual Calculations
    Application.Calculate
    
    'To select a Pivot Table
    Dim PT As PivotTable
    Dim PTF1 As PivotField
    Dim PTF2 As PivotField
    Dim PTV1 As String
    Dim PTV2 As String
        
    Application.ScreenUpdating = True
    
    Set PT = ActiveSheet.PivotTables("ExpAnalysis")
    
    ActiveSheet.PivotTables("ExpAnalysis").PivotFields("Acc").ClearAllFilters
    ActiveSheet.PivotTables("ExpAnalysis").PivotFields("Dt").ClearAllFilters
    
    'It works fine till here

    'Real Need and problem begins
    Set PTF1 = ActiveSheet.PivotTables("ExpAnalysis").PivotFields("Acc")
    Set PTV1 = "CC*"
    Set PTF2 = ActiveSheet.PivotTables("ExpAnalysis").PivotFields("Dt")
    Set PTV2 = Year(Today())
    
    
    PTF1.ClearAllFilters
    PTF2.ClearAllFilters
    PTF1.CurrentPage = PTV1
    PTF2.CurrentPage = PTV2

    'It again goes as intended from here
    
    'To select a particular header in the Pivot Table
    PT.PivotSelect "Dt", xlButton
    
    Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(2, 0)).Select
    
    Application.EnableEvents = False
        ThisWorkbook.RefreshAll
    Application.EnableEvents = True
    
    'To Save my workbook
    ActiveWorkbook.Save
    
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,143,840
Messages
5,721,101
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top