Syntax error (simple)

[Jack Schitt]

I get a compile error "Invalid use of property on the "RefersTo" method.
What am I doing wrong, please?
I am trying to set a locally defined name "Header" on each worksheet in the
workbook to refer to $AZ$102:$BH$147
within the worksheet to which the respective name is localised.

Private Sub Fixnames()
Dim wWS As Worksheet
Dim R As Range
With Application.ThisWorkbook
For Each wWS In .Worksheets
Set R = wWS.Range("$AZ$102:$BH$147")
wWS.Names("Header").RefersTo R
Next wWS
End With 'Application.ThisWorkbook
End Sub 'Fixnames()

 

[Jack Schitt]

Wow! Instant response! thanks.

Jack,

RefersTo is a property, so you need an equals sign to assign a
value to it.

wWS.Names("Header").RefersTo = R


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

Jack,

RefersTo is a property, so you need an equals sign to assign a
value to it.

wWS.Names("Header").RefersTo = R


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

If the name doesn't exist you need to add it. Assuming it does exist:

wWS.Names("Header").RefersTo = "=" & r.Address(External:=True)

 

[Jack Schitt]

Thanks T

If the name doesn't exist you need to add it. Assuming it does exist:

wWS.Names("Header").RefersTo = "=" & r.Address(External:=True)