Run-time Error 1004

[Zwicky]

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.

 

[KenPuls]

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?

Sheets("Pivot").PivotTables("Pivot").PivotFields("C Key"). _CurrentPage = ckey

If so, you may want to remove the _ character

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

http://www.excelguru.ca

 

[Zwicky]

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

 

[KenPuls]

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

http://www.excelguru.ca

 

[Jarvonimbus]

Help! I'm having exactly this problem as well - can anyone help please

Cheers!

 

[SkipVought]

Jarvonimbus,

Please post your question in a new thread.

Please be CLEAR, CONCISE and COMPLETE.

Skip,
[sub]
[red][/red]
[/sub]

 

[Zwicky]

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