Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run-time Error 1004

Status
Not open for further replies.

Zwicky

Technical User
Aug 7, 2006
9
AU
Hi There,

I have a problem with some code which causes a pivot table to change.
The strange thing is that the code worked fine until I updated the pivot table with some new data from Microsoft Access, all the names are the same but all of a sudden I get "Run-time error 1004: Unable to set the _Default property of the PivotItem class"

The code highlighted with debug is the third line below:
Dim ckey As Variant
ckey = Range("CKey")
Sheets("Pivot").PivotTables("Pivot").PivotFields("C Key"). _CurrentPage = ckey

where CKey is the range containing the value I want the Pivot Table Field "C Key" to change to.

I found some suggestions from Microsoft on the web
(1) Insert code "ActiveCell.Activate" - doesn't work
(2)Change the value of the TakeFocusOnClick property to False for the button - there is no TakeFocusOnClick property
(3) Set the Autosort option for the Pivot Table Field to Manual - doesn't work

So frustrated...ahhhh!
Thanks a million to anyone who knows how to fix it.
 
Hi there,

These may be minor, or may not. I haven't tried to set up a PT, and don't use them much, so bear with me...

Your line of code, is it really typed in the VBE exactly like this?
Code:
Sheets("Pivot").PivotTables("Pivot").PivotFields("C Key"). _CurrentPage = ckey

If so, you may want to remove the _ character
Code:
Sheets("Pivot").PivotTables("Pivot").PivotFields("C Key").CurrentPage = ckey

The other thing I notice is that your "CKey" is list as "C Key" in your PivotFields code.

Hopefully one of those makes a difference. If not, we'll have to dig a little deeper. :)

Ken Puls, CMA
 
Hi Ken,

Thanks for your reply.
The underscore is simply the way to continue the code on the next line. I tried taking it out just in case but no go.
CKey is what I've called the field in Excel whereas C Key is the name of the Pivot Field. They are not the same thing. I double checked the spaces are right.

Guess we'll have to keep digging.

Zwicky
 
Hi Zwicky,

I'll have a play a bit later.

Technically speaking, the continuation is actually space, underscore, hard return. The code you had above doesn't have the hard return, which is why I asked. ;)

I have some code lying around somewhere to sync pivot fields, I believe, I just need to find it.

Ken Puls, CMA
 


Jarvonimbus,

Please post your question in a new thread.

Please be CLEAR, CONCISE and COMPLETE.

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
Hi Jarvonimbus,
If it's any help, I discovered my problem actually had to do with the incoming data and the code was fine. I think the data was in the wrong format or there were some blank spaces in there that I didn't realise - something like that.
Good luck!
Zwicky
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top