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]

Is the below true in your case?

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

 

[Bree001]

Mark858 I did not have data in column A. Well called, works perfectly now.

You rock! All the best.

 

[awilpers]

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.

Thank you! I'd like to add another if/then command to this macro. Do i just copy and paste it below the first one? Essentially here's what I want to do, but i keep running into an error that says "compile error - ambiguous name deteced"

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

 

[awilpers]

I don't think i copied both over in the post above - below is what i'm trying to do. The first command copies a row over to one sheet, and the second command moves a row over to another. Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target.Cells.Count = 1 Then
If LCase(Target.Value) = "<=6weeks" Then
With Target.EntireRow
.Copy Sheets("Anticipated Delivery").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End With
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target.Cells.Count = 1 Then
If LCase(Target.Value) = "delivered" Then
With Target.EntireRow
.Copy Sheets("Delivered").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.Delete
End With
End If
End If
End Sub

 

[GWteB]

You can not have two sub's with the same name. Merge your code, for example:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 9 And Target.Cells.Count = 1 Then
    
        If LCase(Target.Value) = "delivered" Then
            With Target.EntireRow
                .Copy Sheets("Delivered").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
        
        If LCase(Target.Value) = "<=6weeks" Then
            With Target.EntireRow
                .Copy Sheets("Anticipated Delivery").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End With
        End If
    End If

End Sub

 

[awilpers]

THANK YOU GWteB!!!

 

[GWteB]

You are welcome!

 

[Prashant1211]

Hello Everyone, I hope you all are doing good and staying safe.
I have similar question like above only difference is i am trying to move the rows to next sheet based on Types. So if value in cell is Type-1 it should go to Sheet1 and so on. number of type varies for different file. So number of sheets to be added based on type. I have very basic knowledge in macros and looking for support. Thank you very much!!

 

[GWteB]

What do you mean with "Types"? As I understand it, different specific words or product names. For each type of product, the entire row must go to its own separate worksheet, all sheets together in one workbook. For example all peanuts on the peanut worksheet, all beers on the beer worksheet, and so on. Is that what you want?
Apparently you have different source files, each with several worksheets, on each sheet the occurence of the different product types.
If you're able to give some more information, for instance about the layout of the source sheets, in which column the product name is, and so on, would be helpful.

 

[Prashant1211]

Hi GWteB, Sorry for late reply. rather than moving the rows to a different sheet i am trying to work on single sheet.
As shown in attached image there are product "Type" (highlighted). i want to create a packing list from this sheet. i want to separate the items based on type and then put some more information in between. The number of items in each type will deviate (means its not always type 2 will have 5 items only). Also there are "n" number of Types. I also attached 2nd image of how the final output should look like. Please let me know if you need more information. Thank you