Automatically move Rows of Data from one sheet to enough based on a status (Remove)

[ExcelNovice2017]

Hi All

I have a register in excel which gets reviewed every 3 months which has 10's of Thousands of lines.

What I would like to be able to do in the status column (AH) is trigger a automation, When the status changes to "Remove"it moves it into a different tab called "Removed" in the same workbook

There is 32 columns starting at (B) and Finishing at (AH), there is potentially 200,000 lines

If possible what I would like to do is when it has been moved to the "removed" tab it captures the date it was move in Column (AI) Called Data Removed

Any help would be much appreciated!

 

[My Aswer Is This]

This script will copy the entire row to sheet named "Removed" when you manually Enter or when you choose from a Data Validation list the value "Remove" In any cell in column "AH"

And will enter todays date in Column "AI" of sheet named "Removed" on same row.

And the script will deleted the Row from the orginal sheet.

You said:

If possible what I would like to do is when it has been ​

moved to the "removed" tab​

it captures ​

the date it was move in Column (AI)​

Called Data Removed

So when you say moved I assume you mean copied to other sheet and removed from original sheet.
And when you say put Todays date in column AI I assume you mean Column A1 of sheet named "Removed"

If I am wrong I will have to re write this script.

Here is the script I have now:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window​

​

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/20/2019  8:37:04 PM  EST
If Target.Column = 34 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
Dim ans As Long
ans = Target.Row
Dim Lastrow As Long
Lastrow = Sheets("Removed").Cells(Rows.Count, "AH").End(xlUp).Row + 1
If Target.Value = "Remove" Then
    Rows(ans).Copy Sheets("Removed").Rows(Lastrow)
    Sheets("Removed").Cells(Lastrow, "AI").Value = Date
    Rows(ans).Delete
End If
End If
Application.EnableEvents = True
End Sub

 

[ExcelNovice2017]

Good Morning

Thank you very much for the code it's much appreciated and going to be a great help going forward, can we just make a slight change to the code?

This is due to me not giving you the right info at the time so my apologises, I'm hoping that this won't be to much of a change

So the Date would be (Col AI Row 5)?

On the Removed Tab the first Row available to copy the data across the is Row 5

 

[ExcelNovice2017]

Hello My Answer is this

I have just tested the code and it works perfectly Thank you!!

 

[My Aswer Is This]

So you just said 5 minutes ago you needed it modified to start on row 5.


I guess not. Glad you have it working for you

 

[ExcelNovice2017]

I just added the code as it was and it works great, Thank you

If I need to do the same for completed in the future, is it possible to use this change but make the relevant changes so the code work based on completed or is there a smarter why to use the code?

I just know once there see this I will be asked at some point to look into it

Cheers for your help