Automatically move rows to another sheet

[BradleyN1]

Hi

I have a spreadsheet of people who attended training

So for example I have 'Sheet 1' with peoples information on, let's say Column B represents 'completed', if entered in this column 'completed' I want that row of information to be transferred automatically to 'Sheet 2' (which would represent a completed training sheet) and along with that delete the blank row it was originally on, on 'Sheet 1'

The two sheets within the same workbook will have the same layout and headings etc. but 'Sheet 2' will basically be a blank worksheet for the info to be transferred onto

Also as the information is being transferred, can the rows which are being moved over fall under each row that has been transferred over (rather than overwriting what has already been transferred)

I hope my explanation suffices but if not please just ask me anymore info, I greatly appreciate any help

P.S I've had a browse on an already existing thread but there's so many pages and people have had errors and it's been edited as the pages go on so I don't know which one actually works, so apologies for that!

 

[MARK858]

I wanted pasted info on Sheet 2 to start from B4 again, so it sits in B4 - N4

Is there already data in B3 on Sheet2?

with M4 being the 'completed'

Are you saying that you are now typing "completed" in column M?

 

[BradleyN1]

Is there already data in B3 on Sheet2?



Are you saying that you are now typing "completed" in column M?

Yes Sheet 2 is basically a copy of the first so again begins from B4, column M is the target column for completed to be inputted now yep!
thanks

 

[MARK858]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M4:M" & Range("B" & Rows.Count).End(xlUp).Row)) Is Nothing And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "completed" Then
            Range(Cells(Target.Row, "B"), Target).Copy Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
            Target.EntireRow.Delete
        End If
    End If
End Sub

 

[BradleyN1]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M4:M" & Range("B" & Rows.Count).End(xlUp).Row)) Is Nothing And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "completed" Then
            Range(Cells(Target.Row, "B"), Target).Copy Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
            Target.EntireRow.Delete
        End If
    End If
End Sub

Works great but just 1 little thing, can you edit it so the info is pasted into Sheet2 from B4, seems to be going in from B2? thanks again

 

[BradleyN1]

I don't know how to edit but I think its actually ok!
Thanks

 

[MARK858]

It'll only start from B2 if you have nothing in B2 or B3 which is why I asked if there was anything in B3.

 

[BradleyN1]

It'll only start from B2 if you have nothing in B2 or B3 which is why I asked if there was anything in B3.

It's actually working great now thank you so much

 

[Ewok246]

Hi, sorry to jump in on this thread but it seemed the most similar to my question that I could find on different threads.
I have a workbook that did have entire rows automatically cut and paste from one sheet into another sheet once I entered a date in Column Q and pressed Enter. For some reason it is not working today and I can't work out what is wrong in my code (I not very good at all at this stuff).

Below is the code as is:

Private Sub Worksheet_change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Worksheets("Completed Jobs").Range("rngDest")

' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Range("rngTrigger")) Is Nothing Then

' Only trigger if the value entred is a date or is recognizable as a valid date
If IsDate(Target) Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End Sub


At the moment Excel is showing the top line "Private Sub Worksheet_change(ByVal Target As Range)" highlighted, so I'm guessing this is the problem? I just have no idea what I need to do to fix it :/

Any help for this code noob will be greatly appreciated!

 

[Ewok246]

Sorry all I worked it out, carry on

 

[Bree001]

Try putting the code below in Sheet1's worksheet module (right click the sheet tab and click view code).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "completed" Then
            With Target.EntireRow
                .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
    End If
End Sub

The code will start "pasting" from Row 2 if you don't have headers and assumes you always have data in column A.

Hi there,

Thank you so much for your solution! I used this code for the same problem and it worked spectacularly - except for one thing. When the row was transferred to Sheet2, it over-wrote the row that had been previously transferred, leaving me always with only one row on Sheet2. Sheet2 is intended to contain a list of all 'completed' projects carried over from Sheet1. Is there a modification that can be made to this code so that each row that is transferred will fall under the one previously transferred?

Thanks so much!