Sailormoon February 2016

VBA Filter error

I have a filter field called week_of_year (1,2,3,4,5,6,7,8....), another pivot field year(2012,2013,2014), the vba code belows filter on the current week and year of 2014/2015 and hide everything else. But the problem is the code loops each record and runs slow when it comes to big pivot table. Im trying on a new code but encountering some error.

Sub datefilter()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("LO").PivotTables("PivotTable3")
Dim dd As Integer
dd = Format(Date, "ww")
Dim pf As PivotField
Dim pf1 As PivotField
Dim PI As PivotItem
Set pf =              

Worksheets("LO").PivotTables("PivotTable3").PivotFields("week_of_year")
Set pf1 =       
Worksheets("LO").PivotTables("PivotTable3").PivotFields("year")
PvtTbl.ClearAllFilters
For Each PI In pf.PivotItems
 If PI.Name = CStr(dd) Then

    PI.Visible = True
Else
    PI.Visible = False
End If
Next

For Each PI In pf1.PivotItems
 If PI.Name = "2014" Or PI.Name = "2015" Then
    PI.Visible = True
Else
    PI.Visible = False
End If
Next
End Sub

New code that I develop is:

Sub datefilter1()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("LO").PivotTables("PivotTable3")
Dim dd As Integer
dd = Format(Date, "ww")
Dim pf As PivotField
Set pf =        
Worksheets("LO").PivotTables("PivotTable3").PivotFields("week_of_year")
pf.PivotFilters.Add2 xlValueEquals, 3
End Sub

The code failed at

pf.PivotFilters.Add2 xlValueEquals, CStr(dd)

I also tried:

pf.PivotFilters.Add2 Type:= xlValueEquals, Value1 := CStr(dd)

The error is Invalid procedure call or argument.Any idea How I can fix this error?????

Any faster way to filter on multiple filter criteria? Thanks!

Answers


jeffreyweir February 2016

If you only want to filter on one value, drag the field of interest to the Report Filter pane, then you can do this very quickly without looping using the following code:

Sub FilterOnWeek()
Dim pt As PivotTable
Dim pf As PivotField


Set pt = Worksheets("LO").PivotTables("PivotTable3")
Set pf = pt.PivotFields("week_of_year")

pf.CurrentPage = Format(Date, "ww")
End Sub

If for some reason this field absolutely must stay in the PivotTable as a row field, you can still filter without looping by the following method if you have Excel 2010 or later

  1. Make a copy of the PivotTable, and drag the 'week_of_year' field in the copy to the Report Filter area
  2. Set up a slicer on the week_of_year field on one PivotTable, and connect it to the other PivotTable via the Report Connections dialog. (Right click on the Slicer, select 'Report Connections')
  3. Amend the above code so it runs on the 2nd PivotTable.

The Slicer will then sync the two PivotTables.

If you want to filter on more than one value, drag the field to the ROWS area, and use this:

Sub FilterOnWeek2()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim lStart As Long
Dim lEnd As Long


lEnd = Format(Date, "ww")
lStart = Val(Format(Date, "ww")) - 1

Set pt = Worksheets("LO").PivotTables("PivotTable3")
Set pf = pt.PivotFields("week_of_year")

With pf
    .ClearAllFilters
    .PivotFilters.Add Type:=xlCaptionIsBetween, Value1:=lStart, Value2:=lEnd
End With

End Sub

If for some reason you don't want this field in the PivotTable as a row field, follow the same general approach as above: make a copy of the PivotTable, put the field that you want to filter on in the copy as a ROWS field, connect the fields of interest together as a slicer, hide the slicer somewhere, and amend the code so it runs on the copy of the PivotTable with the rowfield in it.

Post Status

Asked in February 2016
Viewed 3,251 times
Voted 8
Answered 1 times

Search




Leave an answer