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:

Please Login or Register  to view this content.
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.

I have 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!