Copy certain cells to new sheet based upon a different cell?

[cjizzle]

Hi all, sincerely appreciate the resources and people here. Please forgive my noobness.

I have a master employee excel file that has all of their data spread across multiple columns (sheet name is EmployeeList). I'm trying to write a macro that updates Sheet2 in the same workbook that shows all employees on the list with a hire date of at least one year ago.

- In the EmployeeList sheet their Department is in Column A, last and first names are in columns B and C and their hire date is in Column I.
- I would like Sheet2 to show their Department, last and first names, and hire date in columns A, B, C and D respectively.
- I would also like to be able to rerun the macro at various points throughout the year that adds names to Sheet2 that have achieved their one year anniversary and also removes names that are no longer listed in the EmployeeList sheet.
- I need to use columns E and on in Sheet2 for personal notations on each employee, so hoping any updates to sheet2 will just insert a line for the new additions (or delete the whole line for ex-employees) so that the already existing adjacent data will be on the correct employee.

Any thoughts here? I could upload a basic copy of the employee list with all sensitive details deleted if it would help. Been searching for days trying to figure this out and finally had to break down and ask for help.

Thanks in advance for any assistance you can provide!

CJ

[Kenneth Hobs]

Welcome to the forum!

I think that doing it all on the data sheet would suffice. Use AutoFilter. Add two helper columns. For the main filter column, put this formula for row 2: =DATEDIF(A2,NOW(),"Y")>=1
Then add your comments column.

Or, you can add the two columns and use a macro to do an Advanced Filter that copies filtered data by the DATEDIF() column.

[cjizzle]

Thanks for the welcome and the reply!

Still learning the basics of vba so will research all of the above info. Much appreciate you pointing me in the right direction!

CJ

EDIT: I realize I worded a few things wrong in my first post. Sheet2 is blank. What I was wanting to do was have the vba script copy the employees with 1 year or greater of service over to Sheet2.

[Kenneth Hobs]

As I explained, after adding the two columns, autofilter would suffice for one sheet. If you really wanted to copy it, then advanced filter could be used. I would do that in a macro since you would want to clear all of Sheet2 and then it would filter and copy to sheet2.

One does have to careful as any manual changes to Sheet2 would be deleted after each run of the macro. You can attach a simple macro if you want help. Click the Go Advanced button lower right of a reply, and then the link Manage Attachments bottom of reply box, browse and upload.

This has some good tips for advanced filter. https://www.contextures.com/xladvfilter01.html
Once set up and working manually, a recording would easily get you headed in the right direction. Of course once you attach a simple example file, we can help more easily as well.

[cjizzle]

Thanks again for the reply. Again after a lot of research I realize I have been overcomplicating and miswording things. Apologies. I hope you all can bear with me and lend a hand. I just took a course on basic vba yet the complexity of what I am wanting to do is beyond the scope of that tutorial. So here is a slight change to my wording and what really needs accomplished:

Attached are two workbooks, "EmployeeList" and "Vacations"...both have fake emp data but the columns and rows are correct. The EmployeeList workbook will change every time I pull it from my database...it will include any new hires as well as be missing anyone who may have quit or been terminated.

I need the Vacations file to reflect changes in the Employee List file.

So all the stuff that needs to happen:
- IF the hire date in Column I in "EmployeeList" is >= than 1 yr ago:
- Copy data in that row to "Vacations.xlsx" - A to A, B to B, C to C, E to E, I to D
- If that data already exists in "Vacations" do nothing
- If that data does not exist in "Vacations" add it to the next empty row
- IF the SS# in Column E in "EmployeeList.xlsx" does not match any SS# in Column E in "Vacations" (meaning that person is no longer active), highlight or delete the row from "Vacations".. preferably highlight so I can manually delete.


I've been able to write macros that identify the dates I am looking for, delete duplicate or unwanted rows based upon a certain column (employees that work multiple departments), and sort the data by department at the end. How to implement those in an If THEN statement, copying rows and only copying if missing.... deleting (or making alterations to format of) rows only if missing... thats where I'm lost. I know...lost on the majority of the process lol.

Any help on this is greatly appreciated. And any help will not be just copied and pasted...it will be learned entirely, as I will need to take these concepts to multiple different spreadsheets. So yes I guess I am asking for a little bit of spoonfeeding, but I can assure you that every last line of code you may share I will learn and understand.

Many thanks for your time.

P.S. If the code works right, one employee should be added and one highlighted/deleted in the "Vacations" file.