I think I'd use a different macro to copy/insert the rows.

David McRitchie has one you could review at:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas


Or you could try this version:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim myCell As Range

Set myRng = Me.Range("a:A")

If Intersect(Target, myRng) Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error Resume Next

For Each myCell In Intersect(Target, myRng).Cells
If IsEmpty(myCell) Then
Me.Cells(myCell.Row, "C").ClearContents
Else
With Me.Cells(myCell.Row, "C")
'.FormulaR1C1 _
= "=IF(RC[-2]="""","""",RC[-2]+1-WEEKDAY(RC[-2]+8-2))"
.FormulaR1C1 = "=RC[-2]+1-WEEKDAY(RC[-2]+8-2)"
.NumberFormat = "mm/dd/yyyy"
End With
End If
Next myCell

Application.EnableEvents = True
On Error GoTo 0

End Sub

JulesM wrote:

Many Thanks Dave,

Apologies for the delay in reply. Works a treat, thanks!

I have got one question related to multiple rows.....

If I copy a single row and then paste as a new entry (just for example
purposes) the code is executed and the formula used in column C , end
result Week commencing date is shown....however if i copy multiple
rows, the code doesn't seem to execute and as a result value in C stays
null.

OK...so I saw the following line in your code:
If Target.Cells.Count 1 Then Exit Sub 'one cell at a time

and commented it out.

Now when I paste multiple rows the code is executed but only for the
first row pasted - e.g. paste 4 rows, only row 1 diplays a w/c date in
column C

Any ideas?

Many thanks again.
Jules

--
JulesM
------------------------------------------------------------------------
JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609
View this thread: http://www.excelforum.com/showthread...hreadid=471715

--

Dave Peterson