How would I copy a formula down a column until the last row.  
I have data being output to a workbook and the number of rows change each 
time I run the report.  In cell M2  I wish to enter the formula =sum(L2-K2) 
and then copy this down to the end cell in column M, so M3 =  =sum(L3-K3), M4 
= =sum(L4-K4) and so on 

Hi Mick

Try this for size.  Should do what you want.

take care

Marcus


Sub CopytoLast()
Dim lw As Long

    lw = Range("L" & Rows.Count).End(xlUp).Row
    Range("M2").Value = "=L2-K2"
    Range("M2:M" & lw).FillDown

End Sub

Here is another way to do it (note that FillDown is not used)...

Sub CopytoLast()
  Dim LastRow As Long
  Columns("M").ClearContents
  LastRow = Cells(Rows.Count, "L").End(xlUp).Row
  Range("M2:M" & LastRow).Formula = "=L2-K2"
End Sub

Also note that I clear the contents of Column M before putting the formula 
into the cells... I do this just in case the data Column L is less than it 
was the previous time the macro was run.

-- 
Rick (MVP - Excel)


"mickjjuk" <mickjjuk@discussions.microsoft.com> wrote in message 
news:EF2151DE-807D-422D-803D-123CEC4BC900@microsoft.com...
> How would I copy a formula down a column until the last row.
> I have data being output to a workbook and the number of rows change each
> time I run the report.  In cell M2  I wish to enter the formula 
> =sum(L2-K2)
> and then copy this down to the end cell in column M, so M3 =  =sum(L3-K3), 
> M4
> = =sum(L4-K4) and so on