Macro y función suma

Suponiendo que la hoja se llama Hoja1:
Sub prueba()
Dim r As Range
Set r = Range("Hoja1!" & Left(Mid([Hoja1!a1].Formula, 6), Len(Mid([Hoja1!a1].Formula, 6)) - 1))
[Hoja1!a1].Formula = "=sum(" & r.Resize(r.Rows.Count + 1, 1).Address & ")"
End Sub

Gracias por tu pronta respuesta.
He realizado una prueba, obteniendo el siguiente error:
Error 1004 en tiempo de ejecución:
Error en el método 'range' de objeto '_global'
Leyendo el código, parece que has entendido mi duda a la perfección. ¿Es posible que haya alguna errata o falte algo en el código? ¿O es problema mío? La hoja se llama Hoja1 como bien has supuesto.
Gracias de nuevo.

Pues no sé, a mí me funciona sin problemas. He subido un ejemplo: www.jrgc.es/ejemplos/ejemplo_20110712a.xls

Hola de nuevo,
He probado tu ejemplo y funciona perfectamente. Se me olvidó comentar que yo trabajo en la version 2007 de excel. ¿Puede ser ese el problema por el que no funcione la macro? Ya me ha pasado anteriormente en alguna macro cuando en la empresa actualizamos la versión de office.
El excel al que quiero incorporar la macro es bastante grande, con bastantes gráficos y formatos condicionales, por lo que me gustaría mantener el trabajo en la versión 2007.
Espero no ser muy pesado, me viene realmente bien esta ayuda.

No tengo la versión 2007 de Excel, pero en la 2010 el código funciona también sin problemas: www.jrgc.es/ejemplos/ejemplo_20110712b.xlsm
No tengo forma de verificarlo, pero pienso que debería funcionar también en Excel 2007 puesto que el formato de "libro habilitado para macros" (extensión .xlsm) fue establecido precisamente en dicha versión.