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.

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.

I'm not sure I understand what you're asking. After I name the address, if
the user inserts a row above the range, yes, I need the named range to move
down. Is that what you mean?

"Peter T" wrote:

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.

From what you say I suspect you don't want a 'relative' name, which is what
you were attempting to create. Try this -

Sub Test()
[a4].Select
ActiveWorkbook.Names.Add "RelName", "=A2"
ActiveWorkbook.Names.Add "AbsName", "=$A$2"

Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' A5
Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2

[b6].Select
Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7
Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2

Rows(1).Delete
Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7
Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$1
End Sub

Regards,
Peter T


"ppsa" wrote in message
...
I'm not sure I understand what you're asking. After I name the address, if
the user inserts a row above the range, yes, I need the named range to
move
down. Is that what you mean?

"Peter T" wrote:

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.

OK, thanks, I'll give it a try. By the way, the curious thing to me about
what you're saying is that I got the RefersToR1C1 by recording a macro of me
naming a range. Which worked fine.

"Peter T" wrote:

From what you say I suspect you don't want a 'relative' name, which is what
you were attempting to create. Try this -

Sub Test()
[a4].Select
ActiveWorkbook.Names.Add "RelName", "=A2"
ActiveWorkbook.Names.Add "AbsName", "=$A$2"

Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' A5
Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2

[b6].Select
Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7
Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2

Rows(1).Delete
Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7
Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$1
End Sub

Regards,
Peter T


"ppsa" wrote in message
...
I'm not sure I understand what you're asking. After I name the address, if
the user inserts a row above the range, yes, I need the named range to
move
down. Is that what you mean?

"Peter T" wrote:

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.

Mmmm... not really. Sorry. The only change I made was in the way I assembled
what the macro was doing. No significant changes there. The logic for
creating the range is exactly what the macro is doing. The dollar sign thing
I added as a precaution, there were no dollar signs in the range I recorded,
either. Having said this, I will try what you are suggesting. I'm still
stumped, though, by why the apostrophes are added when they are not in the
variables my code is generating. Why is Excel doing that, I am left to
wonder... Anyway, thank you for your help so far. I'll give it a try tonight.
" :)

"ppsa" wrote:

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.