Does this help?

Sub makerangeandsumattop()
Range(Cells(2, "h"), Cells(5, "h")).Name = "burl"
Cells(1, "H") = Application.Sum(Range("burl"))
'or to leave the formula there
'Cells(1, "H").Formula = "=Sum(burl)"
End Sub


--
Don Guillett
SalesAid Software

"burl_rfc_h" wrote in message
oups.com...
In the code below I created a dynamic range, the range name is based on
the contents in cell 2 of a column (in this case text) and the sheet
name. How can I then place a formula into the 1st cell of the column
that totals up the contents of the dynamic range, using the dynmaic
range name.

Sub Named_Range()

Dim s1 As String
Dim s2 As String
Dim sName As String
s1 = ActiveSheet.Name
s2 = "'" & s1 & "'"

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


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

End Sub

Thanks
Burl