Employees list

I Have an employee list that has over 300 employees. This changes every month as people leave and new one are hired. I need to keep track by entering the new employee in the month that they were hired. I also need to keep a master list. Is there a way to just enter the new employee in to the month that they were hired but have a macro or Visual Basic code to transfer the new data every month to the master list but not delete the list that is there already. It would need to check for any employees that have left as well and update the master list with that. The master list would be the first sheet. Using office 2010. I have attached a sample of what I need.
Thank you.
Employees.xlsx
jodyreidIT ManagerAsked:
Who is Participating?
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
I'm in agreement with metacomputer in the sense that you should keep one central Master List that you will update and edit.  Then, you could have derivative monthly sheets that "pull" the appropriate data (in your case, all employee information for those hired in that month) to their respective sheets.  This is a fairly standard way to set up databases of information and you'll be better-served to use this construction.

Now, your example workbook shows January hire dates in the [Mar] sheet, so I've taken the liberty of changing them to March dates.  I also think your Master List should include the termination date, which could also serve as a "end of status" date (for example, an employee changes roles or locations).  But for the simple purposes here, we'll leave it out.

In my example workbook, all employee information is stored in the [New Employees] sheet.  I've added a new column called "HireMonth" that displays the abbreviation for the month of the Date of Hire.

Then, each month sheet contains a PivotTable that shows just the employees whose month of hire equals the same month (ex., "Jan", "Feb", "Mar").  You do have to refresh those sheets whenever you update the data on the [New Employees] sheet, but you can refresh all PivotTables at once via the menu (PivotTable Tools, Options, Refresh, Refresh All).

-Glenn
EE-Employees.xlsx
0
 
Robert BrownConnect With a Mentor IT ManagerCommented:
Just off the top of my head. I will keep a Master List of all employees with a status field. Active, Terminated, Suspended, Layoff etc. Then have a query report pull based on the status field. An update query could pull the various criteria to another table,if you needed them in a separate historic archive databases. I hope this helped.
0
 
jodyreidIT ManagerAuthor Commented:
Thank you
0
All Courses

From novice to tech pro — start learning today.