If that data.xls workbook is open, you could use:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

With Workbooks("data.xls").Worksheets("Material")
Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
End With

Set myCell = ActiveSheet.Range("A1") 'where the formula goes

myCell.Formula = "=sum(" & myRng.Address(rowabsolute:=False, _
columnabsolute:=False, _
external:=True) & ")"

End Sub

If the data.xls workbook is closed, you can open it and run this kind of
thing--or maybe you could just change your formula so that it uses the entire
column. (You'll have to include the path in your formula, too.)


diepvic wrote:

Hi,
I've got a data excel file which is refreshed everyday. The number of data
rows is varied on each day.
I have a formula " =sum('[Data.xls]Material'!D1:D2000) which links to the
data file. D1 is the first row and D2000 is the last row for 31 May 09, for
example.
Could you please help me how to create a macro to update the formula with
the last row of the data after refreshing the file?

Thanks so much for your attention.

--

Dave Peterson