根据Excel 2007中的单元格值更新数据透视表过滤器 [英] Update Pivot Table Filters based on a Cell Value in Excel 2007
问题描述
你好!
我是VBA的新人,我花了太多时间寻找解决问题的方法。我的猜测是这对您来说是微不足道的:
我想根据单元格值更新数据透视表过滤器。
例如,如果我将我的时间维度中定义的属性年份放入数据透视表过滤器,我想更新此过滤器,使其具有与单元格"M1"相同的值。
在Excel中2003 代码将是这样的:
ActiveSheet.PivotTables(" PivotTable1")。PivotFields(" Year")。CurrentPage = Range(" M1")
但是在Excel 2007中代码不同:
ActiveSheet.PivotTables(" PivotTable1")。PivotFields(" [Time]。[Year]。 [Year]")。CurrentPageName = ???
???应替换为"[时间]。[年]。[2010]"其中Range("M1")= 2010
任何帮助表示赞赏!
P.S。如果我想对Month做同样的事情,并且Cell Value是"March",那么(文字而不是数字), 我的代码需要做哪些更改?
保存单元格值 ;首先将M1转换为变量,然后将其传递给数据透视表过滤器:
pivotfiltervalue = Range(" M1")
ActiveSheet。 PivotTables("PivotTable1")。PivotFields("Year")。CurrentPage = pivotfiltervalue
对不起,我不明白你问题的第二部分。
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?
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.
这篇关于根据Excel 2007中的单元格值更新数据透视表过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!