Filter Excel spreadsheet using VBA

Question

I tried copying and pasting solutions from the Internet forever in order to try to filter the pivot table in Excel using VBA. The code below does not 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 with SavedFamilyCode K123223. I do not want to see any other rows in the pivot table. I want this to work regardless of previous filters. I hope you can help me. Thanks!

---

Based on your post, I try:

 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 a runtime error of 91: an object variable or with a locked block variable. What caused this error?

Answer 1

The .CurrentPage field only works for filter fields (also called page fields).
If you want to filter the row / column field, you need to cycle through the individual elements, for example:

 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 just call:

 FilterPivotField ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode"), "K123223" 

Naturally, this becomes slower the more individual objects in the field. You can also use SourceName instead of Name if it suits your needs better.

Answer 2

Set up the pivot table so that it looks like this:

Now your code can simply work with a range ("B1"), and the pivot table will be filtered to the required SavedFamilyCode

 Sub FilterPivotTable() Application.ScreenUpdating = False ActiveSheet.Range("B1") = "K123224" Application.ScreenUpdating = True End Sub 

Answer 3

You can check it if you want. :)

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 SavedFamilyCode is in a 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.

Answer 4

I think I understand your question. This filters out objects that are in column labels or row labels. The last 2 sections of the code is what you want, but im inserting everything so that you can see exactly how it starts, ending with everything that is defined, etc. I definitely took some of this code from other fyi sites.

At the end of the code, "WardClinic_Category" is the column of my data and the column label of the pivot table. Same thing for IVUDDCIndicator (its column is in my data, but in the row label of the pivot table).

I hope this helps others ... I find it very difficult to find the code that made this the “right way”, and not use code that looks like a 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 

Answer 5

In recent versions of Excel, a new Slicers tool has appeared. Using slicers in VBA is actually more reliable than .CurrentPage (there were error messages while cycling through many filter parameters). Here is a simple example of how you can select the slicer element (remember to remove all unnecessary 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 such as SmartKato to help you customize your dashboards or reports and / or fix your code.

Answer 6

In Excel 2007, you can use much simpler code as follows:

 dim pvt as PivotTable dim pvtField as PivotField set pvt = ActiveSheet.PivotTables("PivotTable2") set pvtField = pvt.PivotFiles("SavedFamilyCode") pvtField.PivotFilters.Add xlCaptionEquals, Value1:= "K123223"