If you are receiving the error 1004 while setting the filters for a pivot table in Excel 2007, you need to add "pivfld.CubeField.EnableMultiplePageItems = True" this line to your code.
dim rgstrMonthNames() as variant
'Dynamically build string array from a list....
rgstrMonthNames() = Array("Jan 2007", "Jan 2006")
For Each ws In Worksheets
For Each pt In ws.PivotTables
Set pivfld = pt.PivotFields("[Dim Date].[MonthNames].[MonthNames]") pivfld.CubeField.ClearManualFilter
pivfld.CubeField.EnableMultiplePageItems = True
pivfld.VisibleItemsList = rgstrMonthNames()
End If
Next
Next
ActiveWorkbook.RefreshAll
Subscribe to:
Post Comments (Atom)
5 comments:
Thanks. This saved me after 3 hours of furious head scratching
Spent a whole day looking for a solution, and I found your post. Thank you!
Yes. This is really helpful.
I have a case where user would pick the values in a pivot table for various filters in Sheet1. Based on the filters he picked, I would like to change the corresponding filters on all other sheets. I am trying to use VisibleItemsList to set the values for other pivot tables.
I am facing issue when I have a dimension with multiple levels example user selects, USA in Country, WA in State and Oregon in city, how do i ensure that the same selection is applied in other pivot tables?
I usually create an update button and attach macro code to it. The code then iterates through every pivottable on every worksheet and sets the filters. Try these web sites: http://www.mrexcel.com/forum/showthread.php?t=395960
http://www.contextures.com/excelfiles.html#Pivot
Post a Comment