Error setting value in 2 pivot tables

[gio123bg]

Hi All,
I'm tying to set a value of a filter of the pivot A on the same filter of the pivot B. More exactly this is my VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B2")) Is Nothing Then
If Sheet3.PivotTables("PivotTable1").PivotFields("ORG").CurrentPage <> "(All)" Then
Sheet6.PivotTables("PivotTable1").PivotFields("ORG").CurrentPage = _
Sheet3.PivotTables("PivotTable1").PivotFields("ORG").CurrentPage
End If
End If

End Sub

When I set a value (the same on both pivot tables) in pivot A, I receive this error message

Run-time error '1004':
Unable to set the _Default property of the PivotItem class [End] [Debug] [Help]

Any help will be well appreciated.

Thanks in advance for your king support.

Regards,

Giovanni

 

[Andrew Poulsom]

Do you have the same PivotItems in both PivotFields?

 

[gio123bg]

Hi Andrew,
first of all thanks for your quick replay and support.

In both pivots I have the same name of the filed (for example "ORG") and the same values related to this field/filter.

Let me know if you need more information.

Thanks in advance.

Regards,

Giovanni

 

[Andrew Poulsom]

What are the PivotItems for ORG in the PivotTable on Sheet3 and which is selected when the event fires? And what are the PivotItems for ORG in the PivotTable on Sheet6 and which is selected?

 

[gio123bg]

Ok
The value that I set in PivotTable1 on Sheet3 for field "ORG" is "OTH". For Sheet6 the value allowed for the filter "ORG" is "OTH". The same value.

If I change the code in

If Not Intersect(Target, Range("B2")) Is Nothing Then
If Sheet3.PivotTables("PivotTable1").PivotFields("ORG").CurrentPage <> "(All)" Then
Sheet6.PivotTables("PivotTable1").PivotFields("Org").CurrentPage = "OTH"
End If
End If

The macro works correctly.

Any idea?

Thanks in advance for your kind support.

Regards,

Giovanni

 

[Andrew Poulsom]

So what happens if you assign Sheet6's CurrentPage to a variable and use that variable in your code?

 

[gio123bg]

Hi Andrew,
Now the macro works correctly using a variable without a type defined.

var1 = Sheet3.PivotTables("PivotTable1").PivotFields("ORG").CurrentPage
Sheet6.PivotTables("PivotTable1").PivotFields("Org").CurrentPage = var1

This is strange but now it's Ok!

Thanks again for your kind support.

Regards,

Giovanni