I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range (which can
vary) into the required R1C1 format so that I end up with something like this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

i hard coded the variable, but see if this will work:

Sub test()
Dim firstrownum As Long
firstrownum = 6
With Worksheets("Sheet2").Cells(firstrownum, 1)
ActiveWorkbook.Names.Add Name:="database", RefersTo:=Range(.Address, _
.End(xlDown).End(xlToRight))
End With
End Sub

--


Gary


"Bob" wrote in message
...
I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range (which can
vary) into the required R1C1 format so that I end up with something like this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

With Worksheets(Sheet2Name)
.Range(.Cells(Sheet2FirstRowNum, 1), _
.Cells(Sheet2FirstRowNum, 1).End(xlDown).End(xlToRight)).Name =
"Database"
End With


"Bob" wrote in message
...
I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range (which
can
vary) into the required R1C1 format so that I end up with something like
this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

Gary,
Your code worked perfectly! Thanks a million!
Bob


"Gary Keramidas" wrote:

i hard coded the variable, but see if this will work:

Sub test()
Dim firstrownum As Long
firstrownum = 6
With Worksheets("Sheet2").Cells(firstrownum, 1)
ActiveWorkbook.Names.Add Name:="database", RefersTo:=Range(.Address, _
.End(xlDown).End(xlToRight))
End With
End Sub

--


Gary


"Bob" wrote in message
...
I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range (which can
vary) into the required R1C1 format so that I end up with something like this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

Thanks for the help. Unfortunately, I received a compile error message
("Invalid or unqualified reference"), and ".Cells" in line 2 of your code was
highlighted.


"-" wrote:

With Worksheets(Sheet2Name)
.Range(.Cells(Sheet2FirstRowNum, 1), _
.Cells(Sheet2FirstRowNum, 1).End(xlDown).End(xlToRight)).Name =
"Database"
End With


"Bob" wrote in message
...
I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range (which
can
vary) into the required R1C1 format so that I end up with something like
this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

Works for me perfectly. I created a sheet name of "Sheet1" and a FirstRowNum
of 5 and created a contiguous block of data.

Rolling back the clock a bit, does this help?

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Name = "Database"

The last line was the difference I was trying to note, which was that
there's no need to use the Names.Add nonsense.

Good luck.



"Bob" wrote in message
...
Thanks for the help. Unfortunately, I received a compile error message
("Invalid or unqualified reference"), and ".Cells" in line 2 of your code
was
highlighted.


"-" wrote:

With Worksheets(Sheet2Name)
.Range(.Cells(Sheet2FirstRowNum, 1), _
.Cells(Sheet2FirstRowNum, 1).End(xlDown).End(xlToRight)).Name =
"Database"
End With


"Bob" wrote in message
...
I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range (which
can
vary) into the required R1C1 format so that I end up with something
like
this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

Thanks! Your revised code block worked perfectly.
Thanks again.
Bob


"-" wrote:

Works for me perfectly. I created a sheet name of "Sheet1" and a FirstRowNum
of 5 and created a contiguous block of data.

Rolling back the clock a bit, does this help?

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Name = "Database"

The last line was the difference I was trying to note, which was that
there's no need to use the Names.Add nonsense.

Good luck.



"Bob" wrote in message
...
Thanks for the help. Unfortunately, I received a compile error message
("Invalid or unqualified reference"), and ".Cells" in line 2 of your code
was
highlighted.


"-" wrote:

With Worksheets(Sheet2Name)
.Range(.Cells(Sheet2FirstRowNum, 1), _
.Cells(Sheet2FirstRowNum, 1).End(xlDown).End(xlToRight)).Name =
"Database"
End With


"Bob" wrote in message
...
I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range (which
can
vary) into the required R1C1 format so that I end up with something
like
this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

If there are no gaps in columns or rows, you could try this:

with worksheets(Sheet2Name)
.cells(sheet2firstrownum,1).currentregion.name _
= "'" & .name & "'!database"
end with

In any case, if you want to make the name local (a sheet level name), you'll
want to add something like:

.name = "'" & worksheets(sheet2name).name & "'!database
or maybe just:
.name = "'" & sheet2name & "'!database

if sheet2name is really the name of the sheet.

Bob wrote:

I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range (which can
vary) into the required R1C1 format so that I end up with something like this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

--

Dave Peterson

Or the modified version to capture single spaces in the sheet:

..name = "'" & Replace(sheet2name,"'","''") & "'!database



"Dave Peterson" wrote in message
...
If there are no gaps in columns or rows, you could try this:

with worksheets(Sheet2Name)
.cells(sheet2firstrownum,1).currentregion.name _
= "'" & .name & "'!database"
end with

In any case, if you want to make the name local (a sheet level name),
you'll
want to add something like:

.name = "'" & worksheets(sheet2name).name & "'!database
or maybe just:
.name = "'" & sheet2name & "'!database

if sheet2name is really the name of the sheet.

Bob wrote:

I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range (which
can
vary) into the required R1C1 format so that I end up with something like
this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

--

Dave Peterson

That's a good way to fix the apostrophes in the worksheet name--not the spaces.

(But it's a good fix!)

- wrote:

Or the modified version to capture single spaces in the sheet:

.name = "'" & Replace(sheet2name,"'","''") & "'!database

"Dave Peterson" wrote in message
...
If there are no gaps in columns or rows, you could try this:

with worksheets(Sheet2Name)
.cells(sheet2firstrownum,1).currentregion.name _
= "'" & .name & "'!database"
end with

In any case, if you want to make the name local (a sheet level name),
you'll
want to add something like:

.name = "'" & worksheets(sheet2name).name & "'!database
or maybe just:
.name = "'" & sheet2name & "'!database

if sheet2name is really the name of the sheet.

Bob wrote:

I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range (which
can
vary) into the required R1C1 format so that I end up with something like
this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

--

Dave Peterson

--

Dave Peterson

Sorry, I meant to type single quotes.

"Dave Peterson" wrote in message
...
That's a good way to fix the apostrophes in the worksheet name--not the
spaces.

(But it's a good fix!)

- wrote:

Or the modified version to capture single spaces in the sheet:

.name = "'" & Replace(sheet2name,"'","''") & "'!database

"Dave Peterson" wrote in message
...
If there are no gaps in columns or rows, you could try this:

with worksheets(Sheet2Name)
.cells(sheet2firstrownum,1).currentregion.name _
= "'" & .name & "'!database"
end with

In any case, if you want to make the name local (a sheet level name),
you'll
want to add something like:

.name = "'" & worksheets(sheet2name).name & "'!database
or maybe just:
.name = "'" & sheet2name & "'!database

if sheet2name is really the name of the sheet.

Bob wrote:

I select a range by using the following code:

Worksheets(Sheet2Name).Cells(Sheet2FirstRowNum, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Is there a way I can "convert" the aforementioned selected range
(which
can
vary) into the required R1C1 format so that I end up with something
like
this:

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Worksheets(Sheet2Name)!R2C1:R18C11"

Any help would be greatly appreciated. Thanks.

--

Dave Peterson

--

Dave Peterson