Use VBA to name Ranges

Mar 7th, 2002, 06:13 PM

Hi guys,
I recorded this macro..
ActiveWorkbook.Names.Add Name:="DATABASE2060", RefersToR1C1:= _
"='2060_DB'!R1C1:R8C9"

Now, I am trying to achieve the same result using VBA.

My code

Set LastCell = ActiveSheet.Range("I65536").End(xlUp)
SV_Code = 2060
Reference = "='" & SV_Code & "_DB!R1C1:" & LastCell.Address(ReferenceStyle:=xlR1C1)

ActiveWorkbook.Names.Add Name:="DATABASE2060", RefersToR1C1:= _
Reference

Running the vba code doesn't achieve the same result. Help guys

Barrie Davidson · Mar 7th, 2002, 06:28 PM

Quote:

On 2002-03-07 17:13, Anonymous wrote:
Hi guys,
I recorded this macro..
ActiveWorkbook.Names.Add Name:="DATABASE2060", RefersToR1C1:= _
"='2060_DB'!R1C1:R8C9"

Now, I am trying to achieve the same result using VBA.

My code

Set LastCell = ActiveSheet.Range("I65536").End(xlUp)
SV_Code = 2060
Reference = "='" & SV_Code & "_DB!R1C1:" & LastCell.Address(ReferenceStyle:=xlR1C1)

ActiveWorkbook.Names.Add Name:="DATABASE2060", RefersToR1C1:= _
Reference

Running the vba code doesn't achieve the same result. Help guys

Try this, I think it will do the trick.

Range("A1", Range("I65536").End(xlUp).Address).Name = "DATABASE2060"

Regards,

Barrie Davidson · Mar 7th, 2002, 06:31 PM

Whoops, make that

Sheets("2060_DB").Range("A1", Sheets("2060_DB").Range("I65536").End(xlUp).Address).Name = "DATABASE2060"

Mar 7th, 2002, 06:42 PM

Thanks

That did the trick!

Mar 7th, 2002, 06:31 PM	#3
Barrie Davidson MrExcel MVP Join Date: Feb 2002 Location: Winnipeg Posts: 2,330	Whoops, make that Sheets("2060_DB").Range("A1", Sheets("2060_DB").Range("I65536").End(xlUp).Address).Name = "DATABASE2060" __________________ Barrie Davidson "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2002, 06:13 PM	#1
Guest Posts: n/a	Hi guys, I recorded this macro.. ActiveWorkbook.Names.Add Name:="DATABASE2060", RefersToR1C1:= _ "='2060_DB'!R1C1:R8C9" Now, I am trying to achieve the same result using VBA. My code Set LastCell = ActiveSheet.Range("I65536").End(xlUp) SV_Code = 2060 Reference = "='" & SV_Code & "_DB!R1C1:" & LastCell.Address(ReferenceStyle:=xlR1C1) ActiveWorkbook.Names.Add Name:="DATABASE2060", RefersToR1C1:= _ Reference Running the vba code doesn't achieve the same result. Help guys

Mar 7th, 2002, 06:42 PM	#4
Guest Posts: n/a	Thanks That did the trick!