I've since ascertained that the issue I had was due to the formatting
of cells, which was putting a space at the end of numeric characters.
Changing the cell format to General or a number format resolved the
issue.

On Mar 7, 5:47 pm, Paul Martin wrote:
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***