Filter Excel pivot table using VBA

Question

I have tried copying and pasting solutions from the internet forever now to try to filter a pivot table in Excel using VBA  The code below doesn t work   Sub FilterPivotTable       Application ScreenUpdating   False     ActiveSheet PivotTables  PivotTable2   ManualUpdate   True     ActiveSheet PivotTables  PivotTable2   PivotFields  SavedFamilyCode   CurrentPage    K123223      ActiveSheet PivotTables  PivotTable2   ManualUpdate   False     Application ScreenUpdating   True End Sub   I want to filter so I see all rows that have SavedFamilyCode K123223  I don t want to see any other rows in the pivot table  I want this to work regardless of the previous filters  I hope you can help me with this  Thanks     Based on your post I m trying   Sub FilterPivotField       Dim Field As PivotField     Field   ActiveSheet PivotTables  PivotTable2   PivotFields  SavedFamilyCode       Value   Range   A 2       Application ScreenUpdating   False     With Field         If  Orientation   xlPageField Then              CurrentPage   Value         ElseIf  Orientation   xlRowField Or  Orientation   xlColumnField Then             Dim i As Long             On Error Resume Next   Needed to avoid getting errors when manipulating fields that were deleted from the data source                Set first item to Visible to avoid getting no visible items while working              PivotItems 1  Visible   True             For i   2 To Field PivotItems Count                 If  PivotItems i  Name   Value Then                        PivotItems i  Visible   True Else                        PivotItems i  Visible   False             Next i             If  PivotItems 1  Name   Value Then                    PivotItems 1  Visible   True Else                    PivotItems 1  Visible   False         End If     End With     Application ScreenUpdating   True End Sub   Unfortunately I get Run time error 91  Object variable or With block variable not set  What has caused this error

User · Answer

Latest versions of Excel has a new tool called Slicers. Using slicers in VBA is actually more reliable that .CurrentPage (there have been reports of bugs while looping through numerous filter options). Here is a simple example of how you can select a slicer item (remember to deselect all the non-relevant slicer values):

Sub Step_Thru_SlicerItems2()
Dim slItem As SlicerItem
Dim i As Long
Dim searchName as string

Application.ScreenUpdating = False
searchName="Value1"

    For Each slItem In .VisibleSlicerItems
        If slItem.Name <> .SlicerItems(1).Name Then _
            slItem.Selected = False
        Else
            slItem.Selected = True
        End if
    Next slItem
End Sub

There are also services like SmartKato that would help you out with setting up your dashboards or reports and/or fix your code.

User · Answer

Field CurrentPage only works for Filter fields  also called page fields    If you want to filter a row column field  you have to cycle through the individual items  like so    Sub FilterPivotField Field As PivotField  Value      Application ScreenUpdating   False     With Field         If  Orientation   xlPageField Then              CurrentPage   Value         ElseIf  Orientation   xlRowField Or  Orientation   xlColumnField Then             Dim i As Long             On Error Resume Next   Needed to avoid getting errors when manipulating PivotItems that were deleted from the data source                Set first item to Visible to avoid getting no visible items while working              PivotItems 1  Visible   True             For i   2 To Field PivotItems Count                 If  PivotItems i  Name   Value Then                        PivotItems i  Visible   True Else                        PivotItems i  Visible   False             Next i             If  PivotItems 1  Name   Value Then                    PivotItems 1  Visible   True Else                    PivotItems 1  Visible   False         End If     End With     Application ScreenUpdating   True End Sub   Then  you would just call   FilterPivotField ActiveSheet PivotTables  PivotTable2   PivotFields  SavedFamilyCode     K123223    Naturally  this gets slower the more there are individual different items in the field  You can also use SourceName instead of Name if that suits your needs better

User · Answer

Configure the pivot table so that it is like this     Your code can simply work on range  B1   now and the pivot table will be filtered to you required SavedFamilyCode  Sub FilterPivotTable   Application ScreenUpdating   False     ActiveSheet Range  B1      K123224  Application ScreenUpdating   True End Sub

User · Answer

In Excel 2007 onwards  you can use the much simpler code using a more precise reference   dim pvt as PivotTable dim pvtField as PivotField  set pvt   ActiveSheet PivotTables  PivotTable2   set pvtField   pvt PivotFields  SavedFamilyCode    pvtField PivotFilters Add xlCaptionEquals  Value1    K123223

User · Answer

I think i am understanding your question  This filters things that are in the column labels or the row labels  The last 2 sections of the code is what you want but im pasting everything so that you can see exactly how It runs start to finish with everything thats defined etc  I definitely took some of this code from other sites fyi    Near the end of the code  the  WardClinic Category  is a column of my data and in the column label of the pivot table  Same for the IVUDDCIndicator  its a column in my data but in the row label of the pivot table     Hope this helps others   i found it very difficult to find code that did this the  proper way  rather than using code similar to the macro recorder    Sub CreatingPivotTableNewData      Creating pivot table Dim PvtTbl As PivotTable Dim wsData As Worksheet Dim rngData As Range Dim PvtTblCache As PivotCache Dim wsPvtTbl As Worksheet Dim pvtFld As PivotField   determine the worksheet which contains the source data Set wsData   Worksheets  Raw Data     determine the worksheet where the new PivotTable will be created Set wsPvtTbl   Worksheets  3N3E     delete all existing Pivot Tables in the worksheet  in the TableRange1 property  page fields are excluded  to select the entire PivotTable report  including the page fields  use the TableRange2 property  For Each PvtTbl In wsPvtTbl PivotTables If MsgBox  Delete existing PivotTable    vbYesNo    vbYes Then PvtTbl TableRange2 Clear End If Next PvtTbl    A Pivot Cache represents the memory cache for a PivotTable report  Each Pivot Table report has one cache only  Create a new PivotTable cache  and then create a new PivotTable report based on the cache    set source data range  Worksheets  Raw Data   Activate Set rngData   wsData Range Range  A1    Range  H1   End xlDown      Creates Pivot Cache and PivotTable  Worksheets  Raw Data   Activate ActiveWorkbook PivotCaches Create SourceType  xlDatabase  SourceData  rngData Address  Version  xlPivotTableVersion12  CreatePivotTable TableDestination  wsPvtTbl Range  A1    TableName   PivotTable1   DefaultVersion  xlPivotTableVersion12 Set PvtTbl   wsPvtTbl PivotTables  PivotTable1     Default value of ManualUpdate property is False so a PivotTable report is recalculated automatically on each change   Turn this off  turn to true  to speed up code  PvtTbl ManualUpdate   True    Adds row and columns for pivot table PvtTbl AddFields RowFields   VerifyHr   ColumnFields  Array  WardClinic Category    IVUDDCIndicator     Add item to the Report Filter PvtTbl PivotFields  DayOfWeek   Orientation   xlPageField    set data field - specifically change orientation to a data field and set its function property  With PvtTbl PivotFields  TotalVerified    Orientation   xlDataField  Function   xlAverage  NumberFormat    0 0   Position   1 End With   Removes details in the pivot table for each item Worksheets  3N3E   PivotTables  PivotTable1   PivotFields  WardClinic Category   ShowDetail   False   Removes pivot items from pivot table except those cases defined below  by looping through  For Each PivotItem In PvtTbl PivotFields  WardClinic Category   PivotItems     Select Case PivotItem Name         Case  3N3E              PivotItem Visible   True         Case Else             PivotItem Visible   False         End Select     Next PivotItem    Removes pivot items from pivot table except those cases defined below  by looping through  For Each PivotItem In PvtTbl PivotFields  IVUDDCIndicator   PivotItems     Select Case PivotItem Name         Case  UD    IV              PivotItem Visible   True         Case Else             PivotItem Visible   False         End Select     Next PivotItem   turn on automatic update   calculation in the Pivot Table PvtTbl ManualUpdate   False   End Sub

User · Answer

You could check this if you like      Use this code if SavedFamilyCode is in the Report Filter    Sub FilterPivotTable      Application ScreenUpdating   False    ActiveSheet PivotTables  PivotTable2   ManualUpdate   True     ActiveSheet PivotTables  PivotTable2   PivotFields  SavedFamilyCode   ClearAllFilters     ActiveSheet PivotTables  PivotTable2   PivotFields  SavedFamilyCode   CurrentPage            K123223     ActiveSheet PivotTables  PivotTable2   ManualUpdate   False   Application ScreenUpdating   True   End Sub   But if the SavedFamilyCode is in the Column or Row Labels use this code    Sub FilterPivotTable        Application ScreenUpdating   False      ActiveSheet PivotTables  PivotTable2   ManualUpdate   True        ActiveSheet PivotTables  PivotTable2   PivotFields  SavedFamilyCode   ClearAllFilters        ActiveSheet PivotTables  PivotTable2   PivotFields  SavedFamilyCode   PivotFilters        Add Type  xlCaptionEquals  Value1   K123223     ActiveSheet PivotTables  PivotTable2   ManualUpdate   False   Application ScreenUpdating   True   End Sub   Hope this helps you

[excel] Filter Excel pivot table using VBA

Examples related to excel

Examples related to filter

Examples related to pivot-table

Examples related to vba