I too have been battling with this dreaded issue, which others clearly
have, but I have not yet found a decent response. I have found a fix,
though I don't know how or why it works.

When I try to pass a seemingly valid value to a CurrentPage, eg:

Set pf = ActiveSheet.PivotTables(1).PivotFields("FiscalYear ")

pf.CurrentPage = Range("FiscalYear").Value

I get the 1004 error. I've tried forcing the value as a string using
Cstr, but nothing changes. When I create the Pivot Table, the default
value of the Pivot Field is "(All)". If I manually change it, the
above code works. The problem seems to be to find a way to
legitimately change the value once progammatically, and then the code
has no hassles. My solution is to pass a PivotItem - any one, it
doesn't matter - then it works.

Set pf = ActiveSheet.PivotTables(1).PivotFields("FiscalYear ")

pf.CurrentPage = pf.CurrentPage.PivotItems(1).Value ' Force any
value into the PivotField
pf.CurrentPage = Range("FiscalYear").Value ' Now push
desired value into the PivotField

I don't know why this works, but it does. I liken it to push-starting
a car that won't go. Pushing the PivotItem.Value is something the
PivotTable recognises and gets it going. Once it's going, the value
from the worksheet is fine. Voila!

I spent a lot of time on this, and I hope this helps others with the
same problem.


Paul Martin
Melbourne, Australia





On Mar 7, 7:24 am, Jitesh Kumar wrote:
Louise, I believe the error is due to the Pivot field name.
It seems you are using the wrong field name. I think here it should be
'Business'.

Just check it.

*** Sent via Developersdexhttp://www.developersdex.com***