In Excel 2007, I have a workbook set up that updates the report filters of multiple pivot tables based on a drop down
selection (created from a Data Validation list). I use the following to change the filter on certain triggers:
ActiveWorkbook.Sheets(SheetVar).PivotTables(PivotVar).PivotFields("MSO_NAME").CurrentPage = MSOVar
Where SheetVar, PivotVar, and MSOVar are set previously. SheetVar and PivotVar are based on a table I've made which contains
names of the sheets and pivots in the workbook and I use some logic to determine when I want certain sheets and pivots
updated. MSOVar is set equal to the value in the data validation list I mentioned.
The problem I am having is
that every once in a while, the code will run to change the MSO_NAME filter, everything will seem to go fine, the filter will
change, but the data in the actual data portion of the pivot table will not change. I've verified that there is data for
both the MSO_NAME that I'm changing from, and the one I'm changing to, so there should be data populated.
also tried refreshing the pivot, changing the filter manually, and changing the data source to the same data source to see if
it would update the data and none of those worked. The only thing I could get to work was to create a new pivot table and
recreate exactly what I had before, when I did that it would display the proper data.
Has anyone ever run into a
similar issue? Does anyone have any suggestions as to what I might be able to do to prevent this problem, or even reproduce
it? I can't seem to figure out what makes it happen as I have multiple pivots in the same workbook and it only happens to
some of them some of the time.
Thanks a ton!