Insert New Employee

**TimlmiT** · 02-14-2018, 02:43 PM

Gurus,

I have scoured the internet for a solution.

Ultimately I am trying to create a macro of which the user enters a new employees first and last name. The macro places that in column "B" and in row by alphabetic order.

User enters: Cosmo Kramer

List (column B)
George Constanza
Jerry Steinfeld

Result

George Constanza
Cosmo Kramer
Jerry Steinfeld

I am using bits and pieces of code I found but I cannot figure out how to define LastnameLP and FirstnameLP variables.

Please help, or let me know if there is an easier way. I need to keep the first and last name in the same cell.

Tia,
Tim

Please Login or Register  to view this content.

**Mumps1** · 02-14-2018, 03:50 PM

I don't think that there is a direct way of doing what you want if both first and last names are in the same cell where the first name comes before the last name and you want to sort by last name. You would have to insert a blank column and place just the last name in that column and then use that column to sort. Once the sorting is done, that new column can be deleted. Another complication is if there are middle names or initials.

**TimlmiT** · 02-14-2018, 04:05 PM

Mumps1,

So, you think if I used some helper cells to split the first and last name into separate cells I could simply run with what I have so far? Just define FirstnameLP as the value of the first name helper cell and the LastnameLP as the last name helper cell?

If so, that would look like firstnameLP = range(helpercell).value?

I am aware of middle names and initials however for the purpose of what I am trying to accomplish, the user has to use only first and last names as a rule.

Tim

**Mumps1** · 02-14-2018, 04:11 PM

If you could post a copy of your file (de-sensitize if necessary) it would be easier to help. Include a detailed explanation of what you want to do using a few examples from your data and referring to specific cells, rows and columns.

**TimlmiT** · 02-14-2018, 04:51 PM

Mumps1,

See the attached spreadsheet.

We could use columns AH and AI for Last and First Names.

Tim

**Mumps1** · 02-14-2018, 05:08 PM

OK. I had a look at your file. Can you now explain step-by-step exactly what you want to do using a few examples. Also could you upload a version of your file that includes the "Save", "Insert Row" and "Employee_Names" macros. We have to make sure that any new macro doesn't interfere with the existing ones.

**TimlmiT** · 02-14-2018, 05:42 PM

Mumps1,

The user will run macro,

the user will enter the first and last name of the new employee into the input box

the macro will look in column "b" to see where the last name of the new employee will fit alphabetically.

the macro will insert a new row (copying all the formulas) and enter the employees name in the column "b" of the new row.

just that easy.... so I thought!

Thanks,
Tim

**Mumps1** · 02-15-2018, 02:22 PM

Have a look at the attached file. You will notice that there are 2 empty rows (rows 19 and 20). Don't delete either of these 2 rows. They are necessary to adjust the formulas correctly. Click the "Add Employee" button at the top. The macro will do the following:
-Prompt you to enter an employee name ... First Name - Last Name
-Prompt you to enter an Employee Number
-Enter that data into the appropriate columns (including AH and AI)
-Copy and adjust the necessary formulas
-Place the names in alphabetical order
-Copy the names to the "Employee" sheet
-Create a new sheet for the newly added employee.
Please let me know how this works out for you

**TimlmiT** · 02-15-2018, 02:34 PM

Mumps!,

Can you upload the file? I tried to paste your code into my workbook and it didn't work. Not sure if you filled names in the AH and AI columns or not.

Thanks.
Tim

**Mumps1** · 02-15-2018, 02:35 PM

The file has been added to Post #8.

**TimlmiT** · 02-15-2018, 03:11 PM

mumps!,

WOW! This is awesome!

Any idea why I am getting a run-time error 424 Object required on line

Please Login or Register  to view this content.

Tim

**TimlmiT** · 02-15-2018, 03:14 PM

Mumps!

Nevermind. I found the error... foundrow should be findrow...

Works GREAT!!

Thank you very much!!!

Tim

**Mumps1** · 02-15-2018, 03:38 PM

You are very welcome.

**TimlmiT** · 02-15-2018, 06:54 PM

Mumps!,

I am trying to learn as I go...
Can you provide a quick explanation of what the (- 2) and (-3) are doing in this code?

Please Login or Register  to view this content.

Also, If I enter Abe Abraham as a new employee, his row is inserted after Greg Clem.

**Mumps1** · 02-16-2018, 09:59 AM

Try the attached file. I have fixed the alphabetizing problem. "Findrow.row" is a variable that represents the row number of the cell containing "Total Hours". The (- 2) and (-3) subtract 2 and 3 respectively from the row number containing "Total Hours". For example, if Findrow.row=20 then subtracting 3 will change its value to 17 which is the value I need because it refers to the row number of the last name in column B. I hope this makes sense.

Insert New Employee

LinkBack

Thread Tools

Rate This Thread

Display

Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Re: Insert New Employee

Thread Information

Users Browsing this Thread

Similar Threads

userfrom consist of 3 list box to insert attendance for employee

convert multiple employee records to one record per employee

Insert Picture In Cell By Matching Employee ID

Generate output employee data from input employee sheet

Excel Automatically Insert Employee Number

insert missing date per employee

Insert Pay Rate Based On Employee Selected

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions