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


--
Return email address is not as DEEP as it appears

Wow! Instant response! thanks.

"Chip Pearson" wrote in message
...
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




"Jack Schitt" wrote in
message ...
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()


--
Return email address is not as DEEP as it appears

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




"Jack Schitt" wrote in
message ...
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()


--
Return email address is not as DEEP as it appears

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

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

--
Regards,
Tom Ogilvy


"Jack Schitt" wrote in message
...
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()


--
Return email address is not as DEEP as it appears

Thanks T

"Tom Ogilvy" wrote in message
...
If the name doesn't exist you need to add it. Assuming it does exist:

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

--
Regards,
Tom Ogilvy


"Jack Schitt" wrote in message
...
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()


--
Return email address is not as DEEP as it appears