Need HELP with code auto move whole row to new worksheet based on data

[crazydoglady]

hi i could really use some help. i have two work sheets outstanding and and i want to be able to automatically hide an entire row based on the text "closed" from my outstanding worksheet and move it to completed.
i know this cant be done with a function or formula and my knowledge of code is awful.
Can someone please help?
my closed column is a drop down not sure if it makes a difference and my rows start in A3

Outstanding
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]any[/TD]
[TD]broken down[/TD]
[TD]belfast[/TD]
[TD]any[/TD]
[TD]closed[/TD]
[TD]1/1/18[/TD]
[/TR]
[TR]
[TD]3858[/TD]
[TD]any[/TD]
[TD]broken down[/TD]
[TD]newyork[/TD]
[TD]any[/TD]
[TD]closed[/TD]
[TD]2/2/18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

When you say "move it to completed" do you want to delete that row in the "Outstanding" sheet or do you want to hide that row?

 

[crazydoglady]

When you say "move it to completed" do you want to delete that row in the "Outstanding" sheet or do you want to hide that row?

Hi sorry had to think about that one. i just want to move it to completed

 

[mumps]

Try this on a copy of your file. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make selection in column F.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "closed" Then
        Target.EntireRow.Copy Sheets("completed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[crazydoglady]

Try this on a copy of your file. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make selection in column F.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "closed" Then
        Target.EntireRow.Copy Sheets("completed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True



Hi i don,t know what i am doing wrong but i cant get it to work,
perhaps if i was to upload my worksheet you might be able to figure out what i am doing wrong? :)

 

[mumps]

You cannot post attachments on this Forum. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[crazydoglady]

Try this on a copy of your file. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make selection in column F.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "closed" Then
        Target.EntireRow.Copy Sheets("completed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True


hi mumps.. its ok all good i got it working perfect it was a capital letter. thanks for your help this has been driving me crazy. i am going to have to learn more about code. thanks you're brilliant :)

 

[crazydoglady]

hi mumps.. its ok all good i got it working perfect it was a capital letter. thanks for your help this has been driving me crazy. i am going to have to learn more about code. thanks you're brilliant ​

​

 

[mumps]

You are very welcome.

 

[crazydoglady]

You are very welcome.

you may have started something mumps lol you might just be might go to person lol