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 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 13, 2019 at 08:38 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Aug 13, 2019 at 08:38 AM
Related:
- Based on the values in cells b77 b81
- Based on the values in cells b77:b81, what function can automatically return the value in cell c77 - Best answers
- Based on the values in cells b77 b81 what function can automatically return the value in cell c77 - Best answers
- Help me to add particular cells ✓ - Excel Forum
- Fill adjacent cell with number from text answer ✓ - Excel Forum
- Based on the values in cells b77 b81 c77 - Excel Forum
- Return Seq Values Until Blank value then return different set of seq values ✓ - Excel Forum
- Excel macro to create new sheet based on value in cells - Guide
2 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Aug 13, 2019 at 02:23 AM
Aug 13, 2019 at 02:23 AM
Hello Ryleecone,
You could use a Worsheet_Change event code to do this:-
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.
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.
Aug 13, 2019 at 08:28 AM
That worked.
Aug 13, 2019 at 08:38 AM
I'm glad to have been able to assist.
Cheerio,
vcoolio.