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]

Hi peter,

Sorry for the delay.
Yes it is overwriting the row, instead of moving the second and next entries to a new row on the new sheet.

Is it posible that if I make a change in sheet on on that specific entry that it will also apply the change to sheet 2. For instance. If I change the date that the date also change in sheet 2 or if I delete the entrie that it will also delete the entrie in sheet 2

Awaiting you kind reponds.
Regards

 

[VoG]

Is there data in column A?

 

[Foxy Lady]

Problem sorted, silly me.

When I change data in sheet one of rows that have already moved to sheet 2 it just add a new row in sheet 2. Is there anyway that the changes can be made to that specific row in sheet 2

 

[VoG]

That isn't easy!

Is there something unique in one of the columns that can be used to match up the row on one sheet with a row on the other?

 

[Foxy Lady]

Yes there is, a number that is linked to that profile.
I did you learn all these codes

 

[VoG]

Which column is it in?

 

[Foxy Lady]

Column H

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Integer, NextLineValue As Variant
Dim NR As Long, F As Range
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
    Set F = Sheets("Master").Columns("H").Find(what:=Range("H" & Target.Row).Value, LookIn:=xlValues, lookat:=xlWhole)
    If F Is Nothing Then
        NR = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    Else
        NR = F.Row
    End If
    Target.EntireRow.Copy Destination:=Sheets("Master").Range("A" & NR)
End If
End Sub

 

[Foxy Lady]

Hi Peter
I have tried this code, but can't seem to get it to work.

I doesn't copy it to the next availible line but to row 33 in Prorisk.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Integer, NextLineValue As Variant
Dim NR As Long, F As Range
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
Set F = Sheets("Master").Columns("H").Find(what:=Range("H" & Target.Row).Value, LookIn:=xlValues, lookat:=xlWhole)
If F Is Nothing Then
NR = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Else
NR = F.Row
End If
Target.EntireRow.Copy Destination:=Sheets("Master").Range("A" & NR)
End If (I have change master to prorisk)
End Sub</pre>

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Integer, NextLineValue As Variant
Dim NR As Long, F As Range
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
    Set F = Sheets("Priorisk").Columns("H").Find(what:=Range("H" & Target.Row).Value, LookIn:=xlValues, lookat:=xlWhole)
    If F Is Nothing Then
        NR = Sheets("Priorisk").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    Else
        NR = F.Row
    End If
    Target.EntireRow.Copy Destination:=Sheets("Priorisk").Range("A" & NR)
End If
End Sub