Intersection of named ranges (same ws) doesn't work.

[Aronax]

Hello!

I'm trying to make a range selection in a pivot table as per the snapshot below:

http://imageshack.us/a/img27/4411/ixaj.jpg

The values I am trying to select are the ones that are both red and italic. (Mobile, Other Fix, Fix) from CBU_NA. I've searched and found a way to select both the rows for CBU_NA category and also the column categ2. However when I try to make an intersection out of the 2 ranges I receive an error. I'm using Excel 2010 on windows 7.

Below the code I am using:

Sub FCST()
Dim r1, r2, r3 As Range

r1 = pt.PivotFields("categ2").DataRange.Select
    Selection.Font.Italic = True

r2 = pt.PivotFields("categ1").PivotItems("CBU_NA").DataRange.EntireRow.Select
    With Selection.Font
        .Color = -36345961
        .TintAndShade = 0
    End With

' error received here where r3 gets assigned. (error 424 - object required)
r3 = Intersect(r1, r2).Value


End Sub

Any suggestion is welcomed as I've been banging my head with this one for a few hours and haven't found a solution to it yet. It seems simple but I'm surely doing something wrong.

Thanks in advance for your help!

 

[Boswell]

I am surprised you are not getting errors earlier because I am accustomed to using the keyword Set to set r1 and r2.. but maybe that is a pivot table thing I am unaware of. The error you are pointing out at the bottom maybe because you are setting a range equal to values. Try r3 = Intersect(r1, r2) - (although I would expect you to have to use Set r3 = Intersect(r1, r2)).

 

[Aronax]

indeed the "Set" was the key many thanks for your reply!