Insert row on value change and copy values from above

[alu]

Hi - I have this code which inserts a blank row everytime there is a change in the value in Column D

[vba]Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row To 2 Step -1
If Cells(lRow, "D") <> Cells(lRow - 1, "D") Then Rows(lRow).EntireRow.Insert
Next lRow
End Sub
[/vba]

What I would like is when it inserts a new row for it to copy the contents B, C & D in the cell above

eg if a new row is inserted in row 14 automatically populate B14, C14 & D14 with the values in B13, C13 & D13.

Any suggestions or pointers? thanks in advance...

[dgt]

Hi alu

Don't know if this will be of use to you, but I use this code to insert a specific row at the point of double clicking the row where it needs to be inserted.

In my case, the specific row contains many formulas, that will update themselves, when copied down.

[VBA]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Offset(0).EntireRow.Insert
ActiveSheet.Rows("18").Copy ActiveCell.Offset(0, 0).EntireRow
End Sub
[/VBA]

Apart from changing the Row number (18); I would probably not be able to help you any further with refining this code.

Hope it helps ...David