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!

 

[GWteB]

Would recommend the following approach. Place the 6 rows that should appear at the bottom of each packing list on a separate worksheet. By the way, I advise you not to use merged cells. Then make sure the data (as visible on your input image) is sorted in the correct column. Then make a copy of your data worksheet. On this copy, perform the following actions:
- skip the first type and select the first item of the second type;
- insert 6 blank rows;
- select the separate worksheet with the 6 rows as mentioned above, select these 6 rows and copy them;
- return to the copied worksheet with data, select the leftmost cell of the first blank line and paste.

- select the first item of the third type;
- insert 6 blank rows;
- ... and so on.

- select the first item of the fourth type;
- insert 6 blank rows;
- ... and so on.

If you like the result, you can record these successive (manual) actions with regard to the fifth type (ie the bottom of the packing list of the fourth type) with the macro recorder.

Place the resulting code in your next post using code tags (VBA/).

 

[Prashant1211]

Thanks a lot. i am almost done with macro to get above output (i will post it once i am 100% done). but i realized i still have to move some items to next sheet including their sub items (which i don't need in my packing list). i can find code to move single row to next sheet but this is not what i need. In my case the no. of sub items vary.
Below is an example of my requirement. for example i want to move item cycle+its sub items to next sheet. So i am looking for a Macro which consider text "Cycle" and select rows (shown yellow) till next empty row in column B. And then move it to next sheet (Sheet2). like this there are more items with sub items i want to move to next sheet. I cannot record the macro and fix the rows because in next packing list i will have different items with sub items in different rows. I am not good in creating Macros and hence appreciate any support i receive to complete my work.

Thank you, i wish you a good health and nice weekend !!

 

[ste33uka]

Hi is there anyway to change the code

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 is there any way to change this code so it copys values and not formulas ?
Thanks

 

[MARK858]

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)(2).PasteSpecial xlValues
                .Delete
            End With
            Application.CutCopyMode = False
        End If
    End If
End Sub

 

[ste33uka]

Thanks alot , that works!

 

[el12345]

Hello,
I am trying to set up a new document to track clients, I have included a screenshot.
For each worksheet, I want to be able to enter the status in column N which will reflect which worksheet it goes to (New Enquiries, Consultations, Quotes To Do, To Book, Live, Booked, Finished). Please can anyone tell me how to do this? Also, it is a problem that I have used a merged cell and table?
Thank you so much!

 

[MARK858]

Welcome to the board, you should really start a new thread as you will get more responses. Click the orange "Post Thread" box on the main Excel Questions page to start your own thread.

 

[el12345]

Welcome to the board, you should really start a new thread as you will get more responses. Click the orange "Post Thread" box on the main Excel Questions page to start your own thread.

View attachment 23428

Thank you! I will do that now.

 

[toeknee0678]

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

Apologies for the newb questions (as well as the age) but how can I edit this to do the following?

(1) Create an "if/then" where the variable is either "Terminated" or "1099" is located in Column C, but each row goes out until Column M -- I tried the following, to no avail...many thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C4:M" & Range("B" & Rows.Count).End(xlUp).Row)) Is Nothing And Target.Cells.Count = 1 Then
If LCase(Target.Value) = "Terminated" Then
Range(Cells(Target.Row, "B"), Target).Copy Sheets("Terminated FTEs").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Target.EntireRow.Delete

If LCase(Target.Value) = "1099" Then
With Target.EntireRow
.Copy Sheets("1099s").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.Delete
End With
End If

End If

End Sub

 

[mat16]

Hi, Sorry for bringing back an old thread but its the best I've found for my issue. I tried this code posted earlier:

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

When I use it as above it works fine, but I need to add another two parts which results in an error. This is what I've tried:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And Target.Cells.Count = 1 Then

If LCase(Target.Value) = "query" Then
With Target.EntireRow
.Copy Sheets("Query").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.Delete
End With
End If

If LCase(Target.Value) = "standard" Then
With Target.EntireRow
.Copy Sheets("standard order").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.Delete
End With
End If

If LCase(Target.Value) = "simple" Then
With Target.EntireRow
.Copy Sheets("simple order").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.Delete
End With
End If

If LCase(Target.Value) = "complex" Then
With Target.EntireRow
.Copy Sheets("complex order").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.Delete
End With
End If
End If

End Sub


Can anyone help?

Thanks