VBA moving rows

[rolfet]

hi all,

I am trying to get rows to move from "active" Sheet to "complete" sheet on the attached file once column "J" has been sent to "complete".

for the life of me i can not get it to work and as its a moving sheet thats updated daily i want it to move rows from one sheet to another so it only shows live jobs.

can anyone help me with the code as i tried using the below and cant get it to work?

Private Sub Worksheet_Change(ByVal Target As Range)

' Check to see only one cell updated
If Target.CountLarge > 1 Then Exit Sub

' Check to see if entry is made in column B after row 5 and is set to "Yes"
If Target.Column = 10 And Target.Row > 2 And Target.Value = "Complete" Then
Application.EnableEvents = False
' Copy columns B to I to complete sheet in next available row
Range(Cells(Target.Row, "B"), Cells(Target.Row, "K")).Copy Sheets("Complete").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
' Delete current row after copied
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End Sub


thanks for any help in advance

 

[Ottsel]

thank you for taking the time to look at this for me. For some reason the code wont work for me. I have opened a new workbook and laid it out from B to K as above and saved it as macro enabled, copied and pasted the above code however it still doesn't work for me? is there a way you can send me your test file and i can save and re-use that one? sorry for being a pain!!

Of course. I'll send it come morning (morning for me at least).

 

[Ottsel]

Here you go:

rolfet_file-VBA moving rows-2024.07.22.xlsm

drive.google.com

 

[rolfet]

Here you go:

rolfet_file-VBA moving rows-2024.07.22.xlsm

drive.google.com

that's great, thank you. Hopefully last question but i noticed this is on google drive. Would the code be different/ not work if i am using microsoft excel?

 

[Ottsel]

that's great, thank you. Hopefully last question but i noticed this is on google drive. Would the code be different/ not work if i am using microsoft excel?

It was created in Microsoft Excel. I just placed it there, so you could access it. Download it and open it with Excel.

 

[Joe4]

rolfet,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.