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" <(E-Mail Removed)> wrote in message
news:9B92BA44-10D1-453C-B8EE-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:9B92BA44-10D1-453C-B8EE-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:58534414-F83E-413B-8E82-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:9B92BA44-10D1-453C-B8EE-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> news:58534414-F83E-413B-8E82-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:9B92BA44-10D1-453C-B8EE-(E-Mail Removed)...
> > > > 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.
> > >
> > >
> > >
>
>
>

> 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.

You may have recorded a macro but then you changed it significantly

Referring to your OP, using the recorded macro as a basis and RefersToR1C1,
all would have worked if you had done

RangeAddress = Range("A20:A450").Address(, , xlR1C1)

It would also have worked had you not removed the $'s in an xlA1 style
address, which the macro did not do.

Regards
Peter T

"ppsa" <(E-Mail Removed)> wrote in message
news:9D5051CB-E376-4CCE-BD74-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:58534414-F83E-413B-8E82-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > > news:9B92BA44-10D1-453C-B8EE-(E-Mail Removed)...
> > > > > 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.

OK, I got it to work, but I'm confused about what I'm seeing. To make it
work, I did two things you suggested: I changed the RefersToR1C1 to RefersTo
and got rid of the replace that removes the dollar signs. Here's what's
confusing me: if I insert a row above the range, the range addresse changes
to accommodate that, just as I want it to. But I would have thought that with
the $ it wouldn't. What am I missing?

thanks, again.


"Peter T" wrote:

> > 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.
>
> You may have recorded a macro but then you changed it significantly
>
> Referring to your OP, using the recorded macro as a basis and RefersToR1C1,
> all would have worked if you had done
>
> RangeAddress = Range("A20:A450").Address(, , xlR1C1)
>
> It would also have worked had you not removed the $'s in an xlA1 style
> address, which the macro did not do.
>
> Regards
> Peter T
>
> "ppsa" <(E-Mail Removed)> wrote in message
> news:9D5051CB-E376-4CCE-BD74-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:58534414-F83E-413B-8E82-(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > > news:9B92BA44-10D1-453C-B8EE-(E-Mail Removed)...
> > > > > > 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.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

> Here's what's
> confusing me: if I insert a row above the range, the range address changes
> to accommodate that, just as I want it to. But I would have thought that
with
> the $ it wouldn't. What am I missing?

Although the $'s makes the address 'absolute the reference updates if you
insert/delete rows/columns. It works just the same in a normal cell formula.
Try say =$D$4 in some cell. Now insert/delete a row/column above or to the
left of D4. The cell formula updates with a new address reference, right?

Normally that's helpful but need to be aware if you delete rows or columns
that fully include the referenced range you will the get a #REF! error.
Again same applies with a ref' in a cell formula ref or using named range
that's been fully deleted.

Anyway glad you got your Names working.

Regards,
Peter T

PS forgot yesterday - I'm sure you know but the apostrophe's surrounding the
sheet name are not only normal but required if the sheet-name includes
certain characters, such as punctuation and spaces.


"ppsa" <(E-Mail Removed)> wrote in message
news:4DADBE08-3224-435E-90D6-(E-Mail Removed)...
> OK, I got it to work, but I'm confused about what I'm seeing. To make it
> work, I did two things you suggested: I changed the RefersToR1C1 to
RefersTo
> and got rid of the replace that removes the dollar signs. Here's what's
> confusing me: if I insert a row above the range, the range addresse
changes
> to accommodate that, just as I want it to. But I would have thought that
with
> the $ it wouldn't. What am I missing?
>
> thanks, again.
>
>
> "Peter T" wrote:
>
> > > 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.
> >
> > You may have recorded a macro but then you changed it significantly
> >
> > Referring to your OP, using the recorded macro as a basis and
RefersToR1C1,
> > all would have worked if you had done
> >
> > RangeAddress = Range("A20:A450").Address(, , xlR1C1)
> >
> > It would also have worked had you not removed the $'s in an xlA1 style
> > address, which the macro did not do.
> >
> > Regards
> > Peter T
> >
> > "ppsa" <(E-Mail Removed)> wrote in message
> > news:9D5051CB-E376-4CCE-BD74-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > > news:58534414-F83E-413B-8E82-(E-Mail Removed)...
> > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > news:9B92BA44-10D1-453C-B8EE-(E-Mail Removed)...
> > > > > > > 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.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >