Code to move rows into another sheet based on certain values [Solved]

Posts
4
Registration date
Monday August 12, 2019
Status
Member
Last seen
August 13, 2019
- - Latest reply: vcoolio
Posts
1249
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
- 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!

See more 

2 replies

Best answer
Posts
1249
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
203
1
Thank you
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.

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6896 users have said thank you to us this month

Ryleecone
Posts
4
Registration date
Monday August 12, 2019
Status
Member
Last seen
August 13, 2019
-
Thank you,

That worked.
vcoolio
Posts
1249
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
203 -
You're welcome Ryleecone.
I'm glad to have been able to assist.

Cheerio,
vcoolio.
Respond to vcoolio
Posts
12892
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
August 24, 2019
1299
0
Thank you
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.

Ryleecone
Posts
4
Registration date
Monday August 12, 2019
Status
Member
Last seen
August 13, 2019
-
Thanks I will check it out.
ac3mark
Posts
12892
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
August 24, 2019
1299 -
You are correct, I edited the link.
Respond to ac3mark