Changing PivotTable Filter with VBA

[elflacoalto]

I am trying to update a pivottable filter based on the value entered in a specified cell. I found the below code at:
http://blogs.msdn.com/gabhan_berry/a...vottables.aspx

I tweaked it to work with my application. The code works beautifully in excel 2007 however many people in my office are still on 2003 and I cannot get it to work. If anyone could point out the probably simple solution that I am missing?

Thanks in advance.

Please Login or Register  to view this content.

[Andy Pope]

Is this the error you get?

"This PivotTable was created in a later version of Excel and can't be updated in this version.

To update it, click OK, and then open the workbook in the version of Excel it was originally created in."

If so you will need to do as it says. Create the pivot table in xl2003.

[elflacoalto]

Actually I don't get any error messages at all. When I converted the file to 2003 I recreated all of the pivot tables. So Excel isn't complaining about that at all.

When I run the code it refreshes all the pivot tables in the workbook without problems, it just doesn't change the filter on the TeamFilter pivot table to reflect the value that was entered in the target range. I can manually go into the pivot table and change the filter and the worksheet works as designed.

I believe there is a problem with how 2003 pivot tables respond to the VBA funtions being used in the macro, although I haven't been able to find out which one it is.

Thanks again in advance.

[Roger Govier]

Hi

Can you post a copy of the Workbook that works in XL2007?

[elflacoalto]

I had to strip some of the data out of this file. But the Macro still works, one of the pivot tables is linked to an external data source so it may error out when you try to run the macro.

Basically the filter value on the TeamFilter pivot table on the Time Breakout tab is suppose to be updated to the value selected in M7 on the Paste Data Here tab. That is the only part that I cannot get to work in 2003.

Thanks again.

[Andy Pope]

If you manually check the PT the EMP_CLASS_1_DESCR field only contains BEN.

[elflacoalto]

That is only because I was short on time when I was stripping the sensitive data and was only able to fabricate that one name in the data source. I will see if I can get some more data fabricated to allow for more than one name in the filter field.

[Hassito]

Does someone has an answer to this problem?