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 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - 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 responses

vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
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 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Aug 13, 2019 at 08:38 AM
You're welcome Ryleecone.
I'm glad to have been able to assist.

Cheerio,
vcoolio.
0