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

K

Kjus

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Pivot Table Error 1
Update pivot table report filter using cell value 0
VBA Filtered Pivot Table Showing "Multiple Values Selected"; Only One Value Selected 0
delete selection in pivotcolumn 3
Format Pivot total rows 2
Macro to update pivot table data range 3
Playing back a Pivot Table macros 2
Using macro to switch 'page' in a pivot table? 1

Top