Code to move rows into another sheet based on certain values
Solved/Closed
nathaliecannell
-
Mar 15, 2017 at 04:25 PM
vcoolio Posts 1371 Registration date Thursday July 24, 2014 Status Moderator Last seen April 12, 2023 - Mar 17, 2017 at 08:02 AM
vcoolio Posts 1371 Registration date Thursday July 24, 2014 Status Moderator Last seen April 12, 2023 - Mar 17, 2017 at 08:02 AM
Related:
- How to move rows automatically in excel based on cell value
- Vba code to move rows to another sheet based on criteria - Best answers
- Move row to another sheet based on cell value - Best answers
- Transfer data from one excel worksheet to another automatically - Guide
- Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
- Excel duplicate rows based on cell value ✓ - Excel Forum
- How to insert rows in excel automatically based on cell value - Excel Forum
- How to change date format in excel - Guide
1 reply
vcoolio
Posts
1371
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 12, 2023
252
Mar 17, 2017 at 08:02 AM
Mar 17, 2017 at 08:02 AM
Hello Nathaliecannell,
You could try the following code:-
The code is a Worksheet_Change event and needs to be placed in the Worksheet (Sheet1) module. Once "Cancelled" is typed into a cell in Column E of Sheet1 and you click away (or press Enter or down arrow), the relevant row of data will be transferred to Sheet2 and the row will be deleted from Sheet1.
To implement the code, right click on the Sheet1 tab and select "View Code" from the menu that appears. In the big white field that then appears, paste the above code.
Test the code in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
You could try the following code:-
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Value = vbNullString Then Exit Sub If Intersect(Target, Columns("E:E")) Is Nothing Then Exit Sub Application.ScreenUpdating = False If Target.Value = "Cancelled" Then Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2) Target.EntireRow.Delete End If Sheet2.Columns.AutoFit Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
The code is a Worksheet_Change event and needs to be placed in the Worksheet (Sheet1) module. Once "Cancelled" is typed into a cell in Column E of Sheet1 and you click away (or press Enter or down arrow), the relevant row of data will be transferred to Sheet2 and the row will be deleted from Sheet1.
To implement the code, right click on the Sheet1 tab and select "View Code" from the menu that appears. In the big white field that then appears, paste the above code.
Test the code in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.