Changing filter of pivot table with a cell value

Jeroenskie · Feb 2nd 2011

Hello,

I'm trying to change the filter of my pivot tables with a value in a cell.
This is working, but I think it is a bit slow.

And with this solution I can not put the pivot tables above each other. And that is what i want.

Code

Sub Macro3()
Dim jaar, maand As String


With ThisWorkbook.Worksheets("Dashboard")
jaar = .Range("C2").Value
maand = .Range("C3").Value
End With




On Error GoTo Errorhandler1
    ActiveSheet.PivotTables("Lijn1").PivotFields("Jaar").ClearAllFilters
    ActiveSheet.PivotTables("Lijn1").PivotFields("Jaar").CurrentPage = jaar


On Error GoTo Errorhandler2
    ActiveSheet.PivotTables("Lijn1").PivotFields("Maand").ClearAllFilters
    ActiveSheet.PivotTables("Lijn1").PivotFields("Maand").PivotFilters.Add Type:= _
        xlCaptionEquals, Value1:=maand


    ActiveSheet.PivotTables("Lijn2").PivotFields("Jaar").ClearAllFilters
    ActiveSheet.PivotTables("Lijn2").PivotFields("Jaar").CurrentPage = jaar


    ActiveSheet.PivotTables("Lijn2").PivotFields("Maand").ClearAllFilters
    ActiveSheet.PivotTables("Lijn2").PivotFields("Maand").PivotFilters.Add Type:= _
        xlCaptionEquals, Value1:=maand


    ActiveSheet.PivotTables("Test").PivotFields("Jaar").ClearAllFilters
    ActiveSheet.PivotTables("Test").PivotFields("Jaar").CurrentPage = jaar


    ActiveSheet.PivotTables("Test").PivotFields("Maand").ClearAllFilters
    ActiveSheet.PivotTables("Test").PivotFields("Maand").PivotFilters.Add Type:= _
        xlCaptionEquals, Value1:=maand


Exit Sub


Errorhandler1:
MsgBox ("Het jaartal is niet bekend.")
Exit Sub


Errorhandler2:
MsgBox ("De maand is niet bekend.")


End Sub

Display More

Does anyone had a solution to have the code in such a way that I can put my pivot tables above each other?
And does anyone know how I can make the code faster?

Thanks in advance!

Herbds7 · Feb 3rd 2011

Re: Changing filter of pivot table with a cell value

Excel 2010 (only)
Multiple PivotTables.
Connected Slicers.
No formulas, no code.
http://c0718892.cdn.cloudfiles…ecloud.com/12_30_10a.xlsx

Jeroenskie · Feb 3rd 2011

Re: Changing filter of pivot table with a cell value

Sorry that I did not mentioned that.
I am working with Excel 2007. I don't have the possibility to use 2010.

So is there another solution?

Jeroenskie · Feb 3rd 2011

Re: Changing filter of pivot table with a cell value

I got the code a lot faster now.

Code

Sub Macro3()
Dim jaar, maand As String


With ThisWorkbook.Worksheets("Dashboard")
jaar = .Range("C2").Value
maand = .Range("C3").Value
End With


Dim pt As PivotTable
        For Each pt In ActiveSheet.PivotTables
                    pt.PivotFields("Jaar").ClearAllFilters
                    On Error GoTo Errorhandler1
                    pt.PivotFields("Jaar").CurrentPage = jaar
                    pt.PivotFields("Maand").ClearAllFilters
                    On Error GoTo Errorhandler2
                    pt.PivotFields("Maand").PivotFilters.Add Type:=xlCaptionEquals, Value1:=maand
        Next pt
Exit Sub


Errorhandler1:
MsgBox ("Het jaartal is niet bekend.")
Exit Sub


Errorhandler2:
MsgBox ("De maand is niet bekend.")


End Sub

Display More

But I still can't put the pivot tables above each other.
Does anyone has a solution for that?

Jeroenskie · Feb 3rd 2011

Re: Changing filter of pivot table with a cell value

I know now what the problem is, but still can't solve it.

The problem is that i have to do this action:

Code

pt.PivotFields("Maand").ClearAllFilters

and then this action:

Code

pt.PivotFields("Maand").PivotFilters.Add Type:=xlCaptionEquals, Value1:=maand

When I clear the filter then the pivot tables overlap each other.

Is there a way to apply a new filter and so replace the old one? Without removing it first.

Jeroenskie · Feb 4th 2011

Re: Changing filter of pivot table with a cell value

Nobody with a solution or something?

Changing filter of pivot table with a cell value

Participate now!

Share