Update Pivot Table Filters based on a Cell Value in Excel 2007 RRS feed

  • Question

  • Hi there!

    I'm new to VBA, and I have spent too much time already searching for solution to my problem. My guess is that this will be trivial for you:

    I want to update Pivot Table Filter based on a Cell Value.

    For example, if I put atribute Year defined in my Time dimension into Pivot Table Filter, I would like to update this Filter so it would have the same value as a Cell "M1".

    In Excel 2003 the code would be something like this:

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").CurrentPage = Range("M1")

    However in Excel 2007 code is different:

    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Time].[Year].[Year]").CurrentPageName = ???

    ??? should be replaced with "[Time].[Year].[2010]" where Range("M1")=2010

    Any help is appreciated!

    P.S. If I want to do the same with Month, and the Cell Value is "March" (text instead of number), what changes do I have to do with my code? 

    Thursday, January 13, 2011 11:53 AM

All replies

  • Save the cell value of M1 into a variable first and then pass it to the pivot table filter:

    pivotfiltervalue = Range("M1")

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").CurrentPage = pivotfiltervalue

    Sorry, I don't understand second part of your question.

    • Proposed as answer by bharaththota Friday, February 4, 2011 1:55 AM
    Wednesday, February 2, 2011 11:53 PM