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

[Roux11]

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

[bakerman2]

As a starter.

Please Login or Register  to view this content.

[jindon]

Try

Please Login or Register  to view this content.

[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

[bakerman2]

Please Login or Register  to view this content.

[Roux11]

Thank you sir Jindon for your response

[jindon]

Have you tried the code after I edited?

[Roux11]

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

[Roux11]

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

[bakerman2]

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Roux11]

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.

[Roux11]

getting error 400 on your code, sir.

[bakerman2]

Please Login or Register  to view this content.

[Roux11]

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?

[bakerman2]

Short answer, "YES".

Thanks for rep+.

[Roux11]

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.

[bakerman2]

Please Login or Register  to view this content.

[Roux11]

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

[bakerman2]

Now why would you want an empty columns between your data if you have no intent on using it ???

[Roux11]

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.

[Roux11]

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

[bakerman2]

Please Login or Register  to view this content.

The 100 is just a columnnumber from which you're certain that it will never be used.
You can change that to any number you would like.

[Roux11]

I got it working. You are awesome! Thank you so much!

[Roux11]

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

[Roux11]

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?

[bakerman2]

Post a relevant example file with the current code you are using.

[Roux11]

Please Login or Register  to view this content.

[bakerman2]

Post an example file in which the error occurs because I can't find anything wrong with the code.