Change Pivot Field Value
 

Hello,
I have got a macro that creates a pivot table, I want to set the table
for default display by selecting one of the options in a field
selection list. For example the field "Year" has got 3 possible
selections (All), 2005, 2006. I want to set the value to 2006.
Recording the action gave me this:-

ActiveSheet.PivotTables("Report").PivotFields("Yea r").CurrentPage = _
"2006"

I inserted this at the end of my existing macro but get the error
"Unable to set the _Default property of the PivotItem class". I
thought this would be a simple addition but cannot seem to get around
this problem.

Any ideas gratefully received.
Thanks.


--
Des
------------------------------------------------------------------------
Des's Profile: http://www.excelforum.com/member.php...o&userid=34154
View this thread: http://www.excelforum.com/showthread...hreadid=542429

Change Pivot Field Value
 

If you run that code as a separate macro after you create the pivottable,
does it work then?

Have you tried it without the quotes around the 2006?

--
Regards,
Tom Ogilvy


"Des" wrote:


Hello,
I have got a macro that creates a pivot table, I want to set the table
for default display by selecting one of the options in a field
selection list. For example the field "Year" has got 3 possible
selections (All), 2005, 2006. I want to set the value to 2006.
Recording the action gave me this:-

ActiveSheet.PivotTables("Report").PivotFields("Yea r").CurrentPage = _
"2006"

I inserted this at the end of my existing macro but get the error
"Unable to set the _Default property of the PivotItem class". I
thought this would be a simple addition but cannot seem to get around
this problem.

Any ideas gratefully received.
Thanks.


--
Des
------------------------------------------------------------------------
Des's Profile: http://www.excelforum.com/member.php...o&userid=34154
View this thread: http://www.excelforum.com/showthread...hreadid=542429

Change Pivot Field Value
 

Thanks Tom. Unfortunately same error when run as a single line of code
separately, removing quotes doesn't help either.

Regards.


--
Des
------------------------------------------------------------------------
Des's Profile: http://www.excelforum.com/member.php...o&userid=34154
View this thread: http://www.excelforum.com/showthread...hreadid=542429

Change Pivot Field Value
 

When i run into this problem I use something like

dim itm as PivotItem
With ActiveSheet.PivotTables("Report").PivotFields("Yea r")
for each itm in .PivotItems
if itm.Value = "2006" then
.CurrentPage = itm.Value
exit for
end if
Next
End With

This is pseudo code and may need some refinement, but the concept of looping
throught he pivot items has been successful for me. It also avoids trying to
assign a value that doesn't exist and can screw up your table.

--
Regards,
Tom Ogilvy





"Des" wrote:


Thanks Tom. Unfortunately same error when run as a single line of code
separately, removing quotes doesn't help either.

Regards.


--
Des
------------------------------------------------------------------------
Des's Profile: http://www.excelforum.com/member.php...o&userid=34154
View this thread: http://www.excelforum.com/showthread...hreadid=542429

Change Pivot Field Value
 

Thanks Tom, this looks like a useful solution.


--
Des
------------------------------------------------------------------------
Des's Profile: http://www.excelforum.com/member.php...o&userid=34154
View this thread: http://www.excelforum.com/showthread...hreadid=542429