Code to move rows into another sheet based on certain values

Solved/Closed
Ryleecone Posts 7 Registration date Monday August 12, 2019 Status Member Last seen September 17, 2019 - Aug 12, 2019 at 12:23 PM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Aug 13, 2019 at 08:38 AM
Hello,

I'm having trouble figuring out how to do something! I have a sheet (Design) with work order details on it (columns A-J will have data in). I want to be able to write "100%" in column G of the row in particular if the customer's contract has been completed and this cause the entire row to be cut and pasted into the completed sheet which will be for finished work orders with the same column headers etc.

I have uplaoded a snap of what I have.

How do I do this? I'm not very good with Macro codes & where to input them!

Related:

2 replies

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 13, 2019 at 02:23 AM
Hello Ryleecone,

You could use a Worsheet_Change event code to do this:-


Private Sub Worksheet_Change(ByVal Target As Range)

        Dim ws As Worksheet: Set ws = Sheets("Completed")

If Intersect(Target, Columns(7)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False

If Target.Value = [100%] Then
        Target.EntireRow.Copy ws.Range("A" & Rows.Count).End(3)(2)
        Target.EntireRow.Delete
End If

Application.ScreenUpdating = True

End Sub


Once you enter 100% into any cell in Column G then click away (or press enter or down arrow), the relevant row of data will be transferred to the Completed sheet and the relevant row of data will be deleted from the Design sheet.
You need to ensure that 100% is the last entry that you make in any row (it would be a good idea to create a data validation drop down list for each cell in Column G just to prevent the possibility of errors).

To implement this code:-

- Right click on the Design sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.
1
Ryleecone Posts 7 Registration date Monday August 12, 2019 Status Member Last seen September 17, 2019
Aug 13, 2019 at 08:28 AM
Thank you,

That worked.
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 13, 2019 at 08:38 AM
You're welcome Ryleecone.
I'm glad to have been able to assist.

Cheerio,
vcoolio.
0
Here is a similar request.

https://ccm.net/forum/affich-1088520-copy-rows-to-other-sheets-based-on-value-in-column

Let us know if you need help with the variables.

0
Ryleecone Posts 7 Registration date Monday August 12, 2019 Status Member Last seen September 17, 2019
Updated on Aug 12, 2019 at 01:13 PM
Thanks I will check it out.
0
Blocked Profile
Aug 12, 2019 at 01:13 PM
You are correct, I edited the link.
0