Simple utility macro to define range name

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

It will fail if you have no data in either Row 1 or Column 1 of Sheet(“Data”) when you run it.

Reply | Quote

Thanks. I neglected to rename Sheet1 to DATA. Now the macro starts off by renaming the active sheet DATA and it works.

Reply | Quote