Syntax error (simple)

J

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()
 
J

Jack Schitt

Wow! Instant response! thanks.

Chip Pearson said:
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
Click to expand...
 
C

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
 
T

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)
 
J

Jack Schitt

Thanks T

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

wWS.Names("Header").RefersTo = "=" & r.Address(External:=True)
Click to expand...
 
Top