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

Report
Posts
7
Registration date
Monday August 12, 2019
Status
Member
Last seen
September 17, 2019
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
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!

2 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
212
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3133 users have said thank you to us this month

Posts
7
Registration date
Monday August 12, 2019
Status
Member
Last seen
September 17, 2019

Thank you,

That worked.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
212
You're welcome Ryleecone.
I'm glad to have been able to assist.

Cheerio,
vcoolio.
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.

Posts
7
Registration date
Monday August 12, 2019
Status
Member
Last seen
September 17, 2019

Thanks I will check it out.
Blocked Profile
You are correct, I edited the link.