Use VBA to name Ranges
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Use VBA to name Ranges

  1. #1
    Guest

    Default

     
    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

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Guest

    Default

      
    Thanks That did the trick!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com