Equating page filter value to a cell drop-down value

[Guest]

Hi,

How do I equate my pivot table page filters to a value in a cell? I have a
macro which refreshes 6 pivot tables contained in a workbook with data
contained in another workbook. I would like my macro to force all 6 pivot
tables to have a page filter which equals to a value (from a drop-down list)
that I have in cell b4 on my Summary sheet. Here a sampe of one of the pivot
table pieces of code:

ActiveSheet.PivotTables("ALL_TABLE").PivotFields("RC").CurrentPage =
Summary!b4

Of course, I can't put "....=Summary!b4" at the end of this piece of code.
How do I force the pivot table called "ALL_TABLE" to have a page filter (or
pivot field called RC) to equal the value that I have in cell b4 on my
Summary sheet?

Thanks in advance,
Kent Lysell

 

[Guest]

Hi,

I think you need to use PageFields instead of PivotFields().
The following works for me. (based on active sheet) :
Sub test()
Dim Wsh as worksheet
Dim pt As PivotTable
Dim pf As PivotField

set Wsh = activesheet
Set pt = wsh.PivotTables(1)
Set pf = pt.PageFields(1)

pf.CurrentPage = Wsh.Range("B4").Value
''' or = Wsh.Parent.Worksheets("Summary").Range("B4").Value
End Sub