Do you have a particular need to name a relative address, in usage it will
be relative to the active cell (eg select A1, name A2, select B3 and the
name will refer to B4). If that's what you want, use RefersTo instead of
RefersToR1C1.

Regards,
Peter T


"ppsa" wrote in message
...
In the following code, RangeNamePrefix = "Jul071" and NewSheet.Name =
"July,
2007 - 1"

================================================== =
Dim NewRangeName As String
Dim RefersTo As String
RangeAddress = Replace(Range("DateValues").Address, "$", "")
RefersTo = "='" & NewSheet.Name & "'!" & RangeAddress
NewRangeName = RangeNamePrefix & "DateValues"

ActiveWorkbook.Names.Add Name:=NewRangeName, RefersToR1C1:=RefersTo
================================================== =

The variables resolve to the following:
RangeAddress: "A20:A450"
RefersTo: "='July, 2007 - 1'!A20:A450"
NewRangeName: "Jul071DateValues"

You would think that after this code runs, the range A20:A450 would be
named
properly, but it's not. When I go to Insert/Name/Define, the range name is
listed corrctly, but the address is wrong. It has apostrophes around the
individual cell addresses, like this:

='July, 2007 - 1'!'A20':'A450'

I'm stumped. Anyone know what's going on here?

Thanks.