Moving rows from one sheet to another according to Status

[warri0r]

I have 7 columns (Company, Service Order, Type, Model, Date, Warranty, and Status). Each column has a drop down list (except the Service Order and Date).

Under Status I have a drop down list with a few options.
Inbound, Checked In, Evaluated, and etc...

I have 4 sheets (Inbound, In_House, In_House2, and Past record), each with it's own table (some with 2 tables). This is how far I have been able to get:
I manage to set a code to add 'New Entries' on the Inbound sheet for incoming repairs with it's corresponding drop down list.

I tried a few codes to move the rows corresponding with the Status column (Inbound, Checked In, Evaluated, Approved, etc...). However, I had no luck and kept getting errors or it would remove all the rows and replace the table on the other sheet with the rows (close but no cigar).

So....a little help on cutting a Row from one table and moving it to another table in another sheet, corresponding to the Status Column. If I can get the first sheet working, I should be able to do the rest.

test1.xlsm

Thank you everyone for looking over my worksheet

[warri0r]

Hello everyone,

I manage to clean up my excel sheet a bit and I did locate a code that would enable me to move a row from one sheet to another according to Column 7 drop down list for each cell.

However, when it moves the Row to the other sheet (In_House) it doesn't place it where I would like it. Some how it continues to place the Rows at Row 15 rather than Row 4.

Here is the code that allows me to cut and paste a Row from one sheet to the other. Can someone guide me on why it is not working how it should and what can I do to fix it?

test1.xlsm

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row

    If Flag = True Then Exit Sub
    
        If Target.Column = 7 Then
        
            If Target.Value = "Checked In" Then
                
                If Not Intersect(Target, Range("G4:G" & LR)) Is Nothing Then
    
            LR = Sheets("In_House").Range("A" & Rows.Count).End(xlUp).Row + 1
            Target.EntireRow.Copy
            Sheets("In_House").Range("A" & LR).PasteSpecial
            Flag = True
            Target.EntireRow.Delete
        End If
            End If
                End If
    Application.CutCopyMode = False
    Flag = False
End Sub

[AB33]

Warrior,
I wrote the above code and was intended for the thread requested. The request was to move the row in the next empty row on sheet in -house which happens to be row 2 in column A.
Please attach a sample, or explain where exactly you want to copy the row to.

[warri0r]

AB33 Much Thanks for your code and your response =)

I've attached the excel sheet. test1.xlsm

I'd like the row to be copied on the second sheet (In_House) and should go on to a table (Waiting for Evaluation). It should go on Row 4in Column A, any additional Rows should fall under each one.

[AB33]

Try this one

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim NR As Long
    
        If Target.Column = 7 Then
        
            If Target.Value = "Checked In" Then
            
            NR = Worksheets("In_House").Range("G8").End(xlUp).Offset(1).Row
            Range("A" & Target.Row & ":G" & Target.Row).Copy Worksheets("In_House").Range("A" & NR)
           
            Target.EntireRow.Delete
             
        End If
            End If
    Application.CutCopyMode = False
   
End Sub

[warri0r]

Great! This did the trick for the most part.

However, the rows on the second sheet (In_House) do not shift down. I've attached my results test2.xlsm

What would need to change on the code so that the rest of the 'Rows' shift down?

Could you point out where the change would take place on the code if I wanted to duplicate this on a different 'Row'?

I'm sorry for the late response. I tried my best to figure it out last night but had no luck.

[AB33]

I am not sure what do you mean by "second sheet (In_House) do not shift down". The code does not insert, or shift rows.

If you want to copy in to different sheet, you need to change the name of the sheet on the code

("In_House"). Needs to be changed to your choice of the name of the sheet

[warri0r]

AB33,

Your code does move the Row from one sheet to the other. However, when it inserts the copied Row on the other sheet, it doesn't shift the rests of the rows on the sheet down. I may be doing something wrong but I just don't see the rows move down.

So to clarify a bit more, it would be very helpful if it shifts the rows down and than it inserts the copied row.

Thank you for your help AB33.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim NR As Long
    
        If Target.Column = 7 Then
        
            If Target.Value = "Checked In" Then
            
            NR = Worksheets("In_House").Range("G8").End(xlUp).Offset(1).Row
            Range("A" & Target.Row & ":G" & Target.Row).Copy Worksheets("In_House").Range("A" & NR)
           
            Target.EntireRow.Delete
             
        End If
            End If
    Application.CutCopyMode = False
   
End Sub

[warri0r]

Can someone continue to help me with the above code?

[warri0r]

Bump.........No response

[warri0r]

Manage to find where to make my adjustments on the code. However, now the code inserts the copied Row at 'Row 10'.

Where would I need to make the change so that it inserts the row at 'Row 8' not Row 10?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim NR As Long
    
        If Target.Column = 7 Then
        
            If Target.Value = "Checked In" Then
            
            NR = Worksheets("In_House").Range("G8").End(xlUp).Offset(1).Row
            Range("A" & Target.Row & ":G" & Target.Row).Copy Worksheets("In_House").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
           
            Target.EntireRow.Delete
             
        End If
            End If
    Application.CutCopyMode = False
   
End Sub