Creating a Dynamic Named Range Using Sheet Name and Column Header
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
|