Please Help with figuring out code for selecting certain a specific group of cells and cutting them and pasting them on a different worksheet (from a

[kbishop94]

The userform is named 'RemoveEmployee'. It has a combobox named 'cboEN';

cboEN lists all the names that are in row 1 within a dynamic named range (“NAMES”) that starts at column H and goes down to the last column with names in it in row 1;

when a name is selected from cboEN, and the user hits the ‘REMOVE EMPLOYEE FROM ACTIVE LIST’ button (cmdMOVE), here is what I need the code to do:

1. Locate the name that was selected in the combobox in row 1 inside the named range “NAMES” on the spreadsheet “Employee Training Matrix”;
2. Select that cell with the name it;
3. Select the immediate cell of that cell that is to the right of it (+1 column);
4. Select those 2 columns and all the cells below those 2 down to row 63
5. Cut
6. Paste them to the end of row 1 on the spreadsheet “NOT ACTIVE”

My code so far for RemoveEmployee userform:

Initialize code:

Private Sub UserForm_Initialize()

Dim cNam As Range
Dim ws As Worksheet

Set ws = Worksheets("List")

For Each cNam In ws.Range("EmployeeNames")
    With Me.cboEN
        .AddItem cNam.Value
    End With
Next cNam

With DTPicker1
.Format = dtpCustom
.CustomFormat = "MM'/'dd'/'yyyy hh':'mm tt"
.UpDown = False
.Value = Now
End With
End Sub

cmdMOVE code:
this is the code that I have that locates the name that is chosen from the combox cboEN and then assigns that name to a value called "Nam_ID". It gets an error on the line that starts with "r = Application.Worksheet... etc" This is as far as I can get. I know next I need to take that Nam_ID and then have then cut the cells starting with that value, over 1 more column, and down to row 63.... but evetything I have tried is not working.

Private Sub cmdMOVE_Click()
[B][COLOR=#008000]' this is the code that I have that locates the name that is chosen from the combox cboEN and assigns that name to "Nam_ID".  [/COLOR][/B]
Dim DynRng As Range, Nam_ID As String, r As Long
Set DynRng = Worksheets("Employee Training Matrix").Range("Names")

Nam_ID = cboEN
r = Application.WorksheetFunction.Match(Nam_ID, DynRng.Resize(, 0), 0)

End Sub

(There is alot more code than that but they just deal mostly with the check boxes and highlighting this one or that one whenever after the cmdMOVE button is selected if one of the required checkboxes isnt selected, then it gets highlighted and tells the user to check one of them first and try again... I have everything shown on the form (see pic of it below) working fine, but I cant get this part figured out, even though I know it shouldnt be that hard to do what I am trying to do... I'm just getting frustrated now at this point. )

Its tough to explain, so I’ll explain it with a couple screen shots that will hopefuly help it make more sense…


1. Here is the worksheet “Employee Training Matrix” that lists names of employees and their associated training date for certain procedures.(red cells indicates their training for that SOP needs updated)

2. Beside each name is a hidden column that (theres a button that the user can show these hidden columns) shows the date of the training.

3. Here is the form (RemoveEmployee) from where the code is to be executed that will remove the selected employee and all their data to another sheet.
The combo box (cboEN) auto populates with all the names that are shown in row 1 in the picture above (the combobox lists all the names that are in a named dynamic range called “NAMES” )
So… when the user selects, say, “Thorn, Kim” from the combobox “cboEN”…

4. It locates the cell on worksheet “Employee Training Matrix” in row 1 for “Thorn, Kim” (again, from the named range “NAMES” and was selected from combobox “cboEN”)
Select that name and the cell next to it;
And select down to row 63 for both of those 2 columns, and cut...

(part 2 in reply below this post)

 

[kbishop94]

5. ...and paste that cut selection onto worksheet “NOT ACTIVE”, at the end of row 1 on that sheet (first empty cell in that row) Like shown

And thats it. Please and Thank You for anyone willing to help me out with this code. I've grown this workbook steadly over the past 2 years now, and this is to be the last important function I need to add to it and then it will be COMPLETE!