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

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

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  lt  gt   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

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

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

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

[excel] Filter Excel pivot table using VBA

Examples related to excel

Examples related to filter

Examples related to pivot-table

Examples related to vba