I'm creating a template in which a user can copy & paste their records and
format it a certain way w/a macro button. One portion of the macro should
create a named range for all records that appear in column J, starting at J4.
However, as you can see from my code the marco is always going to name
whatever is in between J4:J748 (this is how many records are in the document
I am working with).

I can't figure out how to fix it - any suggestions appreciated.

Range("J4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _
"=Sheet1!R4C10:R748C10"
Range("C1").Select

Hi,

Try this

Dim LastRow As Long
Set Sht = Sheets("Sheet1") ' Change to suit
LastRow = Cells(Cells.Rows.Count, "J").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Records2", _
RefersTo:=Sht.Range("J4:J" & LastRow)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Munchkin" wrote:

I'm creating a template in which a user can copy & paste their records and
format it a certain way w/a macro button. One portion of the macro should
create a named range for all records that appear in column J, starting at J4.
However, as you can see from my code the marco is always going to name
whatever is in between J4:J748 (this is how many records are in the document
I am working with).

I can't figure out how to fix it - any suggestions appreciated.

Range("J4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _
"=Sheet1!R4C10:R748C10"
Range("C1").Select

oops,

a bug in that one, try this instead

Dim LastRow As Long
Set Sht = Sheets("Sheet1") ' Change to suit
LastRow = Sht.Cells(Cells.Rows.Count, "J").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Records2", _
RefersTo:=Sht.Range("J4:J" & LastRow)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Try this

Dim LastRow As Long
Set Sht = Sheets("Sheet1") ' Change to suit
LastRow = Cells(Cells.Rows.Count, "J").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Records2", _
RefersTo:=Sht.Range("J4:J" & LastRow)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Munchkin" wrote:

I'm creating a template in which a user can copy & paste their records and
format it a certain way w/a macro button. One portion of the macro should
create a named range for all records that appear in column J, starting at J4.
However, as you can see from my code the marco is always going to name
whatever is in between J4:J748 (this is how many records are in the document
I am working with).

I can't figure out how to fix it - any suggestions appreciated.

Range("J4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _
"=Sheet1!R4C10:R748C10"
Range("C1").Select

Something like this worked for me. It will allow the named range to vary
based on the number of records in column A. Of course it can be altered to
suit individual needs.

Sub dl() '<<<Can change to CommandButton1_Click()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
ActiveWorkbook.Names.Add "TestRange", RefersTo:=rng.Address
End Sub


"Munchkin" wrote in message
...
I'm creating a template in which a user can copy & paste their records and
format it a certain way w/a macro button. One portion of the macro should
create a named range for all records that appear in column J, starting at
J4.
However, as you can see from my code the marco is always going to name
whatever is in between J4:J748 (this is how many records are in the
document
I am working with).

I can't figure out how to fix it - any suggestions appreciated.

Range("J4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _
"=Sheet1!R4C10:R748C10"
Range("C1").Select

Sub makenamedrange()
lr = Cells(Rows.Count, "j").End(xlUp).Row
Cells(4, "j").Resize(lr - 3).Name = "Records2"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Munchkin" wrote in message
...
I'm creating a template in which a user can copy & paste their records and
format it a certain way w/a macro button. One portion of the macro should
create a named range for all records that appear in column J, starting at
J4.
However, as you can see from my code the marco is always going to name
whatever is in between J4:J748 (this is how many records are in the
document
I am working with).

I can't figure out how to fix it - any suggestions appreciated.

Range("J4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _
"=Sheet1!R4C10:R748C10"
Range("C1").Select

Munchkin,

You have already Selected the range you want to name, so the following
should work for you.

ActiveWorkbook.Names.Add Name:="Records2", RefersTo:=Selection.Address

I did not test this. -- Brad E.


"Munchkin" wrote:

I'm creating a template in which a user can copy & paste their records and
format it a certain way w/a macro button. One portion of the macro should
create a named range for all records that appear in column J, starting at J4.
However, as you can see from my code the marco is always going to name
whatever is in between J4:J748 (this is how many records are in the document
I am working with).

I can't figure out how to fix it - any suggestions appreciated.

Range("J4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Records2", RefersToR1C1:= _
"=Sheet1!R4C10:R748C10"
Range("C1").Select