Moving entire row to new sheet when date is entered

[Foxy Lady]

Hi there,

I wonder if any one can help me.

I want to move an entire row in spreadsheet A to the next empty line in Spreadsheet 2 if a date(any) is entered into column J.

Can this be done??

Regards

Foxylady

 

[Foxy Lady]

Can any one assist me?????

 

[VoG]

Try this: right click the tab of spreadsheet A, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 And IsDate(Target.Value) Then
    Application.EnableEvents = False
    With Target.EntireRow
        .Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Delete
    End With
End If
End Sub

Press ALT + Q to return to your sheet.

 

[Foxy Lady]

Hi VoG,
Thank you for the reply. I did paste the code in it copied the entire row to Sheet 2. How ever I don't want the row to be delete from Sheet 1 and this should happen for every entry in Sheet 1 Column J(when a date is entered it should be copied to the next open line in Sheet 2) Can this be done???

 

[VoG]

That makes it somewhat simpler - try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 And IsDate(Target.Value) Then
    Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End Sub

 

[Foxy Lady]

This is exactly what I needed. Thank you very much.

 

[Foxy Lady]

Hi Peter, Now I sit with another problem. I get an error!! I have two Private Sub Worksheet_Change(ByVal Target As Range) codes doing two different things

Can this be joined?

Here they are?


Private Sub Worksheet_Change(ByVal Target As Range)
r = Target.Row
c = Target.Column
If c <> 1 Then Exit Sub
Application.EnableEvents = False
NextLineValue = Cells(r + 2, c)
If NextLineValue = "Total" Then
Rows(r + 1).Insert
End If
Application.EnableEvents = True

End Sub

And the one you did

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And IsDate(Target.Value) Then
Target.EntireRow.Copy Destination:=Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End Sub</pre>
I hope you can assist me in this.

Regards

Olivia

 

[VoG]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Integer, NextLineValue As Variant
r = Target.Row
c = Target.Column
If c = 1 Then
    Application.EnableEvents = False
    NextLineValue = Cells(r + 2, c)
    If NextLineValue = "Total" Then
        Rows(r + 1).Insert
    End If
    Application.EnableEvents = True
ElseIf c = 9 And IsDate(Target.Value) Then
    Target.EntireRow.Copy Destination:=Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End Sub

 

[Foxy Lady]

Hi Peter,

I have one problem. It doesnt seem to apply the date move entire row to the next lines only to the first line. Am I doing something wrong

 

[VoG]

Are you saying that it is overwriting one row on the Master sheet instead of appending a new row?