Move rows to another sheet based on values in column A

amcrawford - Jul 13, 2022 at 10:15 AM
vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
- Jul 14, 2022 at 03:59 AM

Hello,

I am trying to have rows in sheet "KEY" automatically moved to sheet "INACTIVE DRIVERS" if the cell in column A is "Inactive". I have looked through all of the other answers regarding something like this, but I can't seem to get the codes to work and I don't have a lot of experience with macros. Any help would be appreciated!
Windows / Chrome 103.0.0.0

1 reply

vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
250
Updated on Jul 14, 2022 at 04:02 AM

Hello AMCrawford,

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:-

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
  3.     If Target.Count > 1 Then Exit Sub
  4.     If Target.Value = vbNullString Then Exit Sub
  5.     Application.ScreenUpdating = False
  6.     Application.EnableEvents = False
  7.           If Target.Value = "Inactive" Then
  8.                 Target.EntireRow.Copy Sheets("Inactive Drivers").Range("A" & Rows.Count).End(3)(2)
  9.                 Target.EntireRow.Delete
  10.           End If
  11.     Application.EnableEvents = True
  12.     Application.ScreenUpdating = True
  13. 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.

Cheerio,

vcoolio.

0