Solved: Copy 2 cells with formula.

[Pinokkio]

This invoice with the macro:


Private Sub Worksheet_Change(ByVal target As Range)
If target.Column <> 2 Then Exit Sub
If target.Value <> "" Then
Rows(target.Row + 1).EntireRow.Insert
End If
End Sub




Cell B20 inserted a line with the macro.
But the two formulas in columns C and D are not copied ?
Is here a solution for?


All help greatly appreciated.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target.Value <> "" Then
Me.Rows(Target.Row + 1).EntireRow.Insert
Me.Range("C17").Resize(, 2).Copy
Me.Cells(Target.Row + 1, "C").PasteSpecial Paste:=xlPasteFormulas
End If
End Sub

[Pinokkio]

Sorry,

but it select 2 cells (C17 & D17)?
and just copy one formule (column D)?

[Pinokkio]

This does it almost,


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target.Value <> "" Then
Me.Rows(Target.Row + 1).EntireRow.Insert
Me.Range("C17").Resize(, 2).Copy
Me.Cells(Target.Row, "C").PasteSpecial Paste:=xlPasteFormulas
End If
Application.CutCopyMode = False
End Sub

but it select 2 cells (C17 & D17) after running the macro?

[Pinokkio]

[quote=Pinokkio]

This does it,



Private Sub Worksheet_Change(ByVal Target As Range)
Application.CutCopyMode = False
If Target.Column <> 2 Then Exit Sub
If Target.Value <> "" Then
Me.Rows(Target.Row + 1).EntireRow.Insert
Me.Range("C17").Resize(, 2).Copy
Me.Cells(Target.Row, "C").PasteSpecial Paste:=xlPasteFormulas
End If
ActiveCell.Offset(1, -1).Select
End Sub




Thanks for help.

P.

[Pinokkio]

This does it,



Private Sub Worksheet_Change(ByVal Target As Range)
Application.CutCopyMode = False
If Target.Column <> 2 Then Exit Sub
If Target.Value <> "" Then
Me.Rows(Target.Row + 1).EntireRow.Insert
Me.Range("C17").Resize(, 2).Copy
Me.Cells(Target.Row, "C").PasteSpecial Paste:=xlPasteFormulas
End If
ActiveCell.Offset(1, -1).Select
End Sub




Thanks for help.

P.[/quote]

[Pinokkio]

This does it,



Private Sub Worksheet_Change(ByVal Target As Range)
Application.CutCopyMode = False
If Target.Column <> 2 Then Exit Sub
If Target.Value <> "" Then
Me.Rows(Target.Row + 1).EntireRow.Insert
Me.Range("C17").Resize(, 2).Copy
Me.Cells(Target.Row, "C").PasteSpecial Paste:=xlPasteFormulas
End If
ActiveCell.Offset(1, -1).Select
End Sub




Thanks for help.

P.

[Pinokkio]

This does it,



Private Sub Worksheet_Change(ByVal Target As Range)
Application.CutCopyMode = False
If Target.Column <> 2 Then Exit Sub
If Target.Value <> "" Then
Me.Rows(Target.Row + 1).EntireRow.Insert
Me.Range("C17").Resize(, 2).Copy
Me.Cells(Target.Row, "C").PasteSpecial Paste:=xlPasteFormulas
End If
ActiveCell.Offset(1, -1).Select
End Sub




Thanks for help.

P.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target.Value <> "" Then
Me.Rows(Target.Row + 1).EntireRow.Insert
Me.Range("C17").Resize(, 2).Copy
Me.Cells(Target.Row, "C").PasteSpecial Paste:=xlPasteFormulas
End If

Me.Cells(Target.Row + 1, "A").Select

Application.CutCopyMode = False
End Sub

[Pinokkio]

Thanks xld.

P.