Macro to Filter in PIVOT Table.

[harts15]

Hi Guyz,

I am working with a Pivot. That has some 1000 project each having unique alpha numerical project codes and there budgets for the entire year.

I have grouped the multiple projects under Labs. So I want to filter the project codes in the pivot which in turn effects an Chart.

I tried to use macro recording but it returns a Macro with lot of lines pivot item ac039 = false and finally gives an error that the procedure is too large.
I got the below macro. But this un-ticks the project codes one by one and each time the entire report refreshes and the slicers run taking several hours.

I tried using manual update = true but i dont know whether it is kicking in or not. All i need is remove all filters and keep the project codes which I need to have. The project codes may vary from 1 to 10 project codes at a time.

<code style="margin: 0px;">ption Explicit

Sub FilterPivotItems()

Dim PT As PivotTable
Dim PTItm As PivotItem
Dim FiterArr()AsVariant

' use an array to select the items in the pivot filter you want to keep visible
FiterArr = Array("101","105","107")

' set the Pivot Table
Set PT = ActiveSheet.PivotTables("PivotTable3")

' loop through all Pivot Items in "Value" Pivot field
ForEach PTItm In PT.PivotFields("Value").PivotItems
IfNot IsError(Application.Match(PTItm.Caption, FiterArr,0))Then' check if current item is not in the filter array
PTItm.Visible =True
Else
PTItm.Visible =False
EndIf
Next PTItm

EndSub</code>

 

[Trevor G]

Weclome to the forum,

WHat version of Excel are you using as you could consider using Slicers.

 

[harts15]

Hi Trevor.

I tried that also If i create a slicer and recorded a macro for selecting the codes I need. Even then I am getting a long macro.
After running the macro it goes by checking out each code one by one. Making the report refresh. I am actually working in a excel dash board created by some one. He has hidden the raw data. So I dont have the raw data. there are may 20 pivots and several work sheets and slicers connected to the raw data so when i make any change the entire work book gets refreshed taking 1-2 minutes. So I just need to go the Report filter clear all filters. Check mark only a few codes that I need. This will provide a dash board view along with a graph I need to copy that graph and paste it in a separate excel sheet.

 

[harts15]

thank you for the time though all I need is apply the filter with the codes I need. I will mostly record macro for rest of the steps. Thank you again

 

[harts15]

Hi People.

Finally I source a Code that issuer close to what I want but this filters only the value in the cell b47 how do I get it to filter values in a range like a2:a10

Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Set pt = Worksheets("Pivots").PivotTables("PivotTable4")
Set Field = pt.PivotFields("PRN")
NewCat = Worksheets("PivotS").Range("b47").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With