Pivot Table and VBA

[Jarvonimbus]

Hi There,

I have a problem with some code which causes a pivot table to change.
I have a page with drop-down boxes. After these are selected, I hit a button and want it to update three pivot tables which are in the workbook

I get "Run-time error 1004: Unable to set the _Default property of the PivotItem class"

This is the code which I am using to make the first change

Set MyRange3 = Range("Dealer1")

Sheets("Tick List Dealer").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Dealer").CurrentPage = Range("Dealer1")

Very grateful for any help!

 

[stevexff]

Should you be using .Activate rather than .Select on line 2?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[Jarvonimbus]

Thanks Steve
I'm afraid that doesn't change the result - it still falls over when it reaches line 3

 

[SkipVought]

Hi,

All you need is...

    Sheets("Tick List Dealer").PivotTables("PivotTable1").PivotFields("Dealer").CurrentPage = Range("Dealer1")

Don't need to select or activate UNLESS you want the user to view this sheet when the procedure is complete.

So Named Range Dealer1 contains s value that is one if the PivotItems in PivotField Dealer, yes?

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

 

[Jarvonimbus]

Thanks Skip - see what you mean and I've applied it but I'm still getting the error.
Dealer1 does contain a value that is one if the PivotItems in PivotField Dealer.

 

[SkipVought]

Try using pageFields...

    Sheets("Tick List Dealer").PivotTables("PivotTable1").[b]PageFields[/b]("Dealer").CurrentPage = Range("Dealer1")

Is there a sheet, Tick List Dealer containing PivotTable named PivotTable1?

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

 

[Jarvonimbus]

Thanks again
Still no joy I'm afraid
"Is there a sheet, Tick List Dealer containing PivotTable named PivotTable1?" Yes
It has three page fields "Dealer", "Franchise Group" and "Year";column field "Period" and multiple data fields

 

[SkipVought]

I don't know how your PT is contraucted, but keep in mind that a Page selection CAN affect the available choices in other Page selections. So all PivotItems may NOT be available.

For instance suppose that I had

Color of Fruit
Fruit

as my two Page Fields. If I select Red fruit, then tried to assign Banana to the Fruit Page Field, there would be a PivotItem error.

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

 

[PHV]

And what rplacing this:
.CurrentPage = Range("Dealer1")
with this ?
.CurrentPage = MyRange3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886