GeekyGav
11-16-2011, 12:51 AM
Hi all,
I hope you can help?
This is my code... I'm not sure where I'm going wrong but the row that is being inserted does not include any formulas that should be copied from the initial row.
Column B has a VLOOKUP formula and Column C has a =NOW() formula and I'd like these copied down when the new row is automatically inserted.
Any advice would be fantastic!
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Columns(9)) Is Nothing Then
If Target.Cells.Count = 1 Then
If Target.Value <> " " Then
Me.Rows(Target.Row + 1).Insert
Me.Range(Me.Cells(Target.Row, 1), Me.Cells(Target.Row, 9)).Copy Destination:=Sheets(Me.Cells(Target.Row, "A").Value).Cells(Sheets(Me.Cells(Target.Row, "A").Value).Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
Application.CutCopyMode = False
Me.Cells(Target.Row + 1, 1).Activate
End If
End If
End If
End Sub
I hope you can help?
This is my code... I'm not sure where I'm going wrong but the row that is being inserted does not include any formulas that should be copied from the initial row.
Column B has a VLOOKUP formula and Column C has a =NOW() formula and I'd like these copied down when the new row is automatically inserted.
Any advice would be fantastic!
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Columns(9)) Is Nothing Then
If Target.Cells.Count = 1 Then
If Target.Value <> " " Then
Me.Rows(Target.Row + 1).Insert
Me.Range(Me.Cells(Target.Row, 1), Me.Cells(Target.Row, 9)).Copy Destination:=Sheets(Me.Cells(Target.Row, "A").Value).Cells(Sheets(Me.Cells(Target.Row, "A").Value).Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
Application.CutCopyMode = False
Me.Cells(Target.Row + 1, 1).Activate
End If
End If
End If
End Sub