Macro moving records with termination dates to another workbook

I have two workbooks. First is FakeCard, with active employees listed. Second is FakeTermed where employees with termination dates are transferred to.

To keep FakeCard tidy, I would like the employees who have a termination date in the Employees tab moved to the Employees tab in the FakeTermed workbook, and the empty rows left by moving the records from FakeCard removed.

Ideally, this would be after the HR person enters all the terminated employees and then clicks on the "PurgeTerms" button on bottom right of the Review tab. It is not likely the HR person would remember to open or close the FakeTermed workbook, so I would like the macro to open the FakeTermed workbook, transfer the data and then close the FakeTermed workbook.

In the end, any employees with a termination date entered in FakeCard workbook, Employees tab will be moved to the FakeTermed workbook, Employee tab and then any empty rows in FakeCard workbook, Employees tab will be removed.

I hope this is clear

Thanks so much!

FakeTerms.zipFakeCard.zip

Hi jcaynes

It appears as if your "FakeTermed" Workbook is a copy of "FakeCards" Workbook...perhaps that was simply for this exercise.

In "FakeTermed" clear all Formulas/Links from Sheet Employees. The Code assumes WorkBook "FakeCards"and "FakeTermed" are in the same Folder.

Place the Code in a General Module of "FakeCards" and assign it to the Purge Terms Button on "FakeCards", Sheet Review. The Code will Open "FakeTermed" and Close/Save it when processing is finished. It takes a few seconds to run simply because of all the Code and Sheets you have in Book "FakeTermed"...if you don't need it trash it.

Please Login or Register  to view this content.

You're welcome...glad I could help.