Code to move rows into another sheet based on certain valuesSolved/Closed

Question

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!

ac3mark · Answer

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.

vcoolio · Answer

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.

Code to move rows into another sheet based on certain values

2 responses

Similar discussions

Subscribe To Our Newsletter!