Hi All,

I'm writing some code that automatically changes PageField Values in a
PivotTable. On one of them, I get a Run Time Error 1004 "Unable to set the
_Default property of the PivotItem Class".

Does anyone know what this means in this context? Below is a section of code
that I'm using, and it works ok on another field that I am doing it on...

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Wk_Ending").CurrentPage
= _
"07/07/2007"

I can't say if this will solve your problem or not, but
I have found it best to loop through the pivotitems in the page field and
find the pivotitem that contains the value you want to use, then set the
currentpage to the value of the pivotitem. This eliminates the chance of
screwing up your pivot table by assigning a non-existent item.

--
Regards,
Tom Ogilvy




"DarrenS" wrote:

Hi All,

I'm writing some code that automatically changes PageField Values in a
PivotTable. On one of them, I get a Run Time Error 1004 "Unable to set the
_Default property of the PivotItem Class".

Does anyone know what this means in this context? Below is a section of code
that I'm using, and it works ok on another field that I am doing it on...

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Wk_Ending").CurrentPage
= _
"07/07/2007"

Cheers Tom,

I know that the item is definitely there, because I also tried recording the
macro to make sure that the code, field names and values are exactly correct.
Playing back the recording still gives the same error...?


"Tom Ogilvy" wrote:

I can't say if this will solve your problem or not, but
I have found it best to loop through the pivotitems in the page field and
find the pivotitem that contains the value you want to use, then set the
currentpage to the value of the pivotitem. This eliminates the chance of
screwing up your pivot table by assigning a non-existent item.

--
Regards,
Tom Ogilvy




"DarrenS" wrote:

Hi All,

I'm writing some code that automatically changes PageField Values in a
PivotTable. On one of them, I get a Run Time Error 1004 "Unable to set the
_Default property of the PivotItem Class".

Does anyone know what this means in this context? Below is a section of code
that I'm using, and it works ok on another field that I am doing it on...

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Wk_Ending").CurrentPage
= _
"07/07/2007"

That's cool Darren. I set up a pivot table and recorded the code and got this:

Sub ABC()
'
' Macro3 Macro
' Macro recorded 07/09/2007 by OGILVTW
'

'
ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("Date").CurrentPage = _
"07/07/2007"
End Sub

and it gave me an error as well (same as you).

I tried my advice:

Sub eFG()
Dim pi As PivotItem
With ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("Date")
For Each pi In .PivotItems
If CDate(pi.Value) = CDate("07/07/2007") Then
.CurrentPage = pi.Value
End If
Next
End With
End Sub

And its working like a champ. I am changing dates in the code and it works
great. The only sad part is thinking about you sitting around with your
recorded code getting errors.

What actually does work (besides my way) going back to your old recorded
code way (at least for me) is to do

Sub ABC()
'
' Macro3 Macro
' Macro recorded 07/09/2007 by OGILVTW
' Modified by OGILVYTW after examining what the
' value of the pivot item actually is.

'
ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("Date").CurrentPage = _
"7/7/2007"
End Sub

that worked, but the suggestion I made previously works without finding out
what it is actually looking for.

--
Regards,
Tom Ogilvy


"DarrenS" wrote:


Cheers Tom,

I know that the item is definitely there, because I also tried recording the
macro to make sure that the code, field names and values are exactly correct.
Playing back the recording still gives the same error...?


"Tom Ogilvy" wrote:

I can't say if this will solve your problem or not, but
I have found it best to loop through the pivotitems in the page field and
find the pivotitem that contains the value you want to use, then set the
currentpage to the value of the pivotitem. This eliminates the chance of
screwing up your pivot table by assigning a non-existent item.

--
Regards,
Tom Ogilvy




"DarrenS" wrote:

Hi All,

I'm writing some code that automatically changes PageField Values in a
PivotTable. On one of them, I get a Run Time Error 1004 "Unable to set the
_Default property of the PivotItem Class".

Does anyone know what this means in this context? Below is a section of code
that I'm using, and it works ok on another field that I am doing it on...

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Wk_Ending").CurrentPage
= _
"07/07/2007"

Wow, still no joy...

You know, I have a funny feeling there may be a problem with my PivotTables.
You see, I reaise now that it worked when I first did it, but when I ran one
of my Macros it started giving errors. I believe it could be something to do
with data types. The date in my source data isn't picked up by excel as a
date, so I convert the column to dates using the 'text to columns' function.
Does that sound like a culprit to you?

I'm gonna re-build my PivotTables and see how it goes and let you know.

Cheers
Darren

"Tom Ogilvy" wrote:

That's cool Darren. I set up a pivot table and recorded the code and got this:

Sub ABC()
'
' Macro3 Macro
' Macro recorded 07/09/2007 by OGILVTW
'

'
ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("Date").CurrentPage = _
"07/07/2007"
End Sub

and it gave me an error as well (same as you).

I tried my advice:

Sub eFG()
Dim pi As PivotItem
With ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("Date")
For Each pi In .PivotItems
If CDate(pi.Value) = CDate("07/07/2007") Then
.CurrentPage = pi.Value
End If
Next
End With
End Sub

And its working like a champ. I am changing dates in the code and it works
great. The only sad part is thinking about you sitting around with your
recorded code getting errors.

What actually does work (besides my way) going back to your old recorded
code way (at least for me) is to do

Sub ABC()
'
' Macro3 Macro
' Macro recorded 07/09/2007 by OGILVTW
' Modified by OGILVYTW after examining what the
' value of the pivot item actually is.

'
ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("Date").CurrentPage = _
"7/7/2007"
End Sub

that worked, but the suggestion I made previously works without finding out
what it is actually looking for.

--
Regards,
Tom Ogilvy


"DarrenS" wrote:


Cheers Tom,

I know that the item is definitely there, because I also tried recording the
macro to make sure that the code, field names and values are exactly correct.
Playing back the recording still gives the same error...?


"Tom Ogilvy" wrote:

I can't say if this will solve your problem or not, but
I have found it best to loop through the pivotitems in the page field and
find the pivotitem that contains the value you want to use, then set the
currentpage to the value of the pivotitem. This eliminates the chance of
screwing up your pivot table by assigning a non-existent item.

--
Regards,
Tom Ogilvy




"DarrenS" wrote:

Hi All,

I'm writing some code that automatically changes PageField Values in a
PivotTable. On one of them, I get a Run Time Error 1004 "Unable to set the
_Default property of the PivotItem Class".

Does anyone know what this means in this context? Below is a section of code
that I'm using, and it works ok on another field that I am doing it on...

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Wk_Ending").CurrentPage
= _
"07/07/2007"