Thanks again Ron. Very useful. In the end I used:

Sub EndCell()
Dim Lr As Long
Lr = Sheets("SAP (2)").Range("A" & Rows.Count).End(xlUp).Offset(0, 0).Row
Range("C2").Select
Selection.Copy
Cells(Lr, "C").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

This copied the formula in C2 from the last row containing data up to C2 as
hoped.

Thanks again.

"Ron de Bruin" wrote:

Copy the in a normal module
Run the sub test then

Sub test()
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
MsgBox Lr
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Or without the function

Sub test2()
Dim Lr As Long
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox Lr
End Sub


You can use Cells now like this to build a range
Cells(Lr, "B") for example



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Thanks Ron. I copied this into VBA however it debugged stating that it
expected an end sub after the Sub Macro()??

Also, could I do something a bit more simple like using xldown then that
cell = activecell. Then my formula can copy down to range(activecell)?

"Ron de Bruin" wrote:

Hi Ant

You can use this function fir finding the last row with data on thye worksheet

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Use this in your code then

Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1


If you can check one column then use this
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ant" wrote in message ...
Each month I add new data to an existing sheet. I then want to run a macro to
insert a new column and a formula. Trouble is this data increases rows each
month and I need to be able to identify the final row each time so I can copy
the formula down. ie in Jan the data finished at row 100. In Feb it finishes
at row 200. I need the macro to be able to know to copy the formula down to
row 200 in Feb for example.