need help moving info from one sheet to another sheet based on criteria

[MariahD24]

I am trying to move the information from sheet 1 to sheet 2 once column H is completed. I have tried codes from similar questions but with no luck.

[arlu1201]

Do you want to retain the information in sheet1 or should it be cut pasted to sheet2?

[MariahD24]

I would like it to cut and paste to sheet 2. On e it is complete I no longer want it in sheet 1

[arlu1201]

What will be the contents of column H?

[MariahD24]

There are a few things it could be such as the words sent, completed, paid, etc. Can I set it up so if there are words in the column then it will read as completed and go to sheet 2 or does it have to be one specific word such as "completed?"

[arlu1201]

You can tell me which option you want? Either ways it can be coded. But the code will differ slightly. So just tell me which option you want - either as long as the cell is not empty it should go to sheet 2 or it should show as "completed" to go to sheet2.

[MariahD24]

I would like to have it with the first option, as long as the cell is not empty.
Thanks!

[arlu1201]

I hope this is the last question Will the data in column H be typed in or selected from a dropdown?

[MariahD24]

the information in column H will be typed

[arlu1201]

Right click on the sheet and select View Code. Paste this code in the code window -

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 Then
        Rows(Target.Row & ":" & Target.Row).Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Rows(Target.Row & ":" & Target.Row).ClearContents
    End If
End Sub

[MariahD24]

I tried it and for some reason nothing happened. Is there something I could be doing wrong?

[arlu1201]

As soon as column H will be filled up in the row, you will see that the row has vanished from Sheet1 and is in Sheet2.

[MariahD24]

I keep getting Run-time error '9': Subscript out of range when I type something in column H and hit enter

[arlu1201]

Can you tell me the name of the destination sheet?

[MariahD24]

Sheet 1 is named "Customer Response Needed" sheet 2 is named "Completed Issues." Sheet 2 is the destination sheet.

Thanks

[arlu1201]

Change your code to

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 Then
        Rows(Target.Row & ":" & Target.Row).Copy Worksheets("Completed Issues").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Rows(Target.Row & ":" & Target.Row).ClearContents
    End If
End Sub

[MariahD24]

I don't understand. I right click on "Customer Response Needed" Tab, click view code, paste the code you gave me and still nothing happens. Am I missing a crucial step?

Mariah

[MariahD24]

Ok so I figured it out and the code worked! Thanks so much for your help.

[arlu1201]

Am glad it worked. I will mark your thread as solved.