If you want it dynamic based on the name of the active sheet

If I run this

Sub abc()
Dim s1 As String
Dim s2 As String
Dim sName As String
s1 = ActiveSheet.Name
s2 = "'" & s1 & "'"
sName = Cells(2, 1).Value
ThisWorkbook.Names.Add Name:=sName & "_" & s1, _
RefersTo:="=OFFSET(" & s2 & "!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNTA(" & s2 & "!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"

Debug.Print sName & "_" & s1, ThisWorkbook.Names( _
sName & "_" & s1).RefersTo

End Sub

I get
Test_Here_MSG =OFFSET(MSG!$E$2,1,0,COUNTA(MSG!$E:$E)-1,1)

A2 contains "Test_Here (you can't have a space in the name)

The activecell was in Column E


In your original request, I believe you said you wanted to the beginning of
the name from the cell in the first row of the column containing the
activecell. This modification looks specifically at A2 as you now state.

--
Regards,
Tom Ogilvy


"burl_rfc_h" wrote in message
oups.com...
Tom,

I substituted the actual sheet name in the following code, the formula
is now correct, but the range name only came up with "_MSG", in cell A2
I had the following text "test here", so therefore the range name
should have been "test_here_MSG", (I used to quotation marks in the
preceeding example just to hightlight the area of concern)

Dim sName As String

sName = Cells(1, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_MSG", _
RefersTo:="=OFFSET(" & sName & "MSG!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNTA(" & sName & "MSG!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"

Thanks
Burl