VBA code to move entire row based on value/s from input box

Hi!

I would like to ask help on a VBA code to help me move an entire row to another worksheet based on a value (or multiple values) from an input box. For example I have a list of employees and I want to move one or several of them to the terminated sheet without going through the list one by one or searching by ctrl + F and then cut and paste to the terminated tab. I would like to have an input box where I can just put the employee id (or employee id's of there are more than one) and then it will find the employee from the active employees tab, cut the row and paste it to the terminated tab.

I have attached a sample file.

Your help is greatly appreciated!

Regards,
Roux11

works perfectly! Thank you so much!

Can I have a message box pop out to confirm the employees that were moved to terminated and if there is an employee id that was not found in the list?
Example:

The following employee id's were moved to Terminated Tab:
123
456

The following employee id's were not found:
754
543

Thank you again

Thank you sir Jindon for your response

Have you tried the code after I edited?

Thank you. This is perfect! I love this group!

Not yet, sir Jindon. I will try to use it on another workbook. Thank you again.

Just a minor tweak needed. I used the code to the actual sheet with around 20 columns. It seems to be copying only 3 columns.

getting error 400 on your code, sir.

Thank you! It is now working

If I will do it in reverse (terminated tab to active tab), should I just interchange the tab names in the code?

If I just want to move 2 columns with data, how do I do that? From my example, if I want to move employee 123 to the terminated tab but I want the Name column to be blank for privacy.

Thank you for the updated code. It works in omitting the name however it moves the city to the name column.

I have the same set of column headers in the terminated sheet in which other reports are linked to so I do not want to remove any columns... just want to leave it blank.

Sir, how do I keep just columns 1,5,9 & 15 when moving employee to terminated tab?

I got this code from another source and need to have the employee id show up on the message box like the one you made. With this code, the employee id shows right after the question mark. It would be better to have it like a list below the question especially if there are several employee id's to be deleted. I also love the notification you made that states what employee id's were deleted and the employee id's not found from the list.

Sub delete_Me()
Dim LastRow As Long, x As Long
Dim resp
resp = InputBox("Enter AC Id of rater to delete", "Delete")
If resp = "" Then Exit Sub
MsgBox ("Are you sure you want to permanently delete this employee from the roster?" & resp)
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
If IsDate(resp) Then
resp = CDate(resp)
End If
For x = LastRow To 1 Step -1
If IsDate(resp) Then
If Cells(x, "A").Value = resp Then
Rows(x).Delete
End If
End If

If Not IsDate(resp) Then
If UCase(Cells(x, "A").Value) = UCase(resp) Then
Rows(x).Delete
End If
End If
Next
End Sub

Hi Excel gurus,

Just a follow up on the code I received from you guys regarding moving entire rows based on user input. Everything works well, thanks to all of you. Upon testing a list of employee id's it was noticed that the id's below the one that wasn't found isn't processed. For example I entered 10 employee ids and the 5th one was not found in the sheet. The function stops there and does not process employee ids 6th to 10th. Is there any code that can be supplied to let the function continue along the list if there are id's not found in the sheet?

Please Login or Register  to view this content.