Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I regularly use a Defined Range name DATA that I have to create manually. I'm trying to create a macro in Personal.xls to be attached to a button, that will do this for me. The below does not work. It bombs at the last line, and when I look in my test workbook, I do not see the range name as having been created anyway. It shows it defined for a Personal.xls sheet and cell! TYIA.
    Sub DefineDATA()
    '
    ' DefineDATA Macro
    ' Range("A1").Select
    ActiveWorkbook.Names.Add Name:="DATA", RefersToR1C1:= _
    "=OFFSET(Data!R1C1,0,0,COUNTA(Data!C1),COUNTA(Data !R1))"
    Application.Goto Reference:="DATA"
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your code works correctly for me: it creates the name and selects the range it refers to.

    I'd quit and restart Excel and try it again; hopefully it'll work then.

  3. #3
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It will fail if you have no data in either Row 1 or Column 1 of Sheet("Data") when you run it.

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. I neglected to rename Sheet1 to DATA. Now the macro starts off by renaming the active sheet DATA and it works.

Posting Permissions

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