Moving entire row to new sheet when date is entered

Foxy Lady

New Member
Joined
Feb 25, 2009
Messages
41
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try this: right click the tab of spreadsheet A, select View Code and paste in

Code:
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

New Member
Joined
Feb 25, 2009
Messages
41
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

Legend
Joined
Jun 19, 2002
Messages
63,651
That makes it somewhat simpler - try:

Code:
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

New Member
Joined
Feb 25, 2009
Messages
41
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Try this:

Code:
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

New Member
Joined
Feb 25, 2009
Messages
41
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Are you saying that it is overwriting one row on the Master sheet instead of appending a new row?
 

Forum statistics

Threads
1,085,340
Messages
5,383,050
Members
401,813
Latest member
Lucy_Wood

Some videos you may like

This Week's Hot Topics

Top