Move rows to another sheet based on values in column A
vcoolio Posts 1371 Registration date Thursday July 24, 2014 Status Moderator Last seen April 12, 2023 - Jul 14, 2022 at 03:59 AM
- Move rows to another sheet based on values in column A
- How to copy data from one sheet to another in excel automatically - Guide
- Based on the values in cells b77 b81 - Excel Forum
- Excel move rows to another sheet based on criteria ✓ - Excel Forum
- Excel macro to create new sheet based on value in cells - Guide
Updated on Jul 14, 2022 at 04:02 AM
Assuming that the data in the "Key" sheet starts in row2 with headings in row1, try the following code placed into the "Key" sheet module:-
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
- If Target.Count > 1 Then Exit Sub
- If Target.Value = vbNullString Then Exit Sub
- Application.ScreenUpdating = False
- Application.EnableEvents = False
- If Target.Value = "Inactive" Then
- Target.EntireRow.Copy Sheets("Inactive Drivers").Range("A" & Rows.Count).End(3)(2)
- End If
- Application.EnableEvents = True
- Application.ScreenUpdating = True
- End Sub
To implement this code:-
- Right click on the "Key" sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.
Each time that "Inactive" is typed into a cell in Column A (or, better still, is selected from a data validation drop down list), and you click away, the relevant row of data will be transferred to the "Inactive Drivers" sheet and then deleted from the "Key" sheet.
Using a drop down list for the criteria will make the event instant by removing the need to 'click away' .
I hope that this helps.