Set Value to a Named Range

[detriez]

Since I cannot use a formula as report criteria for a Pivot Table I'm trying to set the value to a named range

Dashboard!A1 is my named range = repBrand
I want to push the value of repBrand to the pivot table criteria in IT Detail!D1

Can someone tell me what I'm missing here?

TIA

Private Sub Testing_Click()


Dim rRange As Range
    On Error Resume Next
        Set rRange = ThisWorkbook.Names("repBrand").RefersTo
Sheets("IT Detail").Range("D1").Value = rRange.Value


End Sub

 

[JoeMo]

Try:

Set rRange = ThisWorkbook.Range("repBrand")

</pre>

 

[detriez]

Thanks for your reply JoeMo
This throws a Compile error: method or data member not found at .Range

 

[JoeMo]

Thanks for your reply JoeMo
This throws a Compile error: method or data member not found at .Range

If repBrand is a named range with a global scope then try this:

set rRange = Range("repBrand")

 

[detriez]

If repBrand is a named range with a global scope then try this:

set rRange = Range("repBrand")

No errors but the value does not change

 

[JoeMo]

No errors but the value does not change

What value doesn't change?