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?
This question is related to
excel
filter
pivot-table
vba
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
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
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.
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.
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.
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"
Source: Stackoverflow.com