Hi,
I’m in a bit of a bind on finding out how to accomplish my goal which I’ll describe below. I’ve spent many hours finding the code I currently have but need to round out the code and need some advice. Any help would be very much appreciated.
I have an ongoing need to take a worksheet full of what is usually about 500-1500 rows in Sheet1 and move or copy each of those rows to a separate worksheet based on a cell value in column A of Sheet1 which is an employee’s name. There are upwards of 40 or so different employee names within Column A in Sheet1 so each employee will have many rows associated with their name. My goal is to have a macro that will create a new worksheet for each employee and then copy the rows that correspond to their name (in Column A) from Sheet1 to their newly created worksheet. The goal beyond this would be to have a new workbook created from each of the worksheets to be emailed to the employee.
I have code in place that copies all of Column A in Sheet1 and pastes it to Column A in Sheet2. It then removes duplicates leaving a single instance of each employee’s name in column A of Sheet2. An email address populates in column B in Sheet2 based on the employee’s name in the cell to the left of it.
The existing code also creates a new worksheet for each employee name present in column A in Sheet2. Not every employee we have will be present in this workbook each time I run the code and we also add employees periodically so the quantity of new worksheets created could be anywhere from 10 to 40+ depending on how many employees were present in that given list.
I’m hoping for some guidance on code that would:
1) Copy the employee’s name and email address from Column A & B in Sheet2 and paste it in Cells $A$1 and $B$2 of the employee’s respective worksheet. This data will be used later. Then,
2) Copy all of the rows from Sheet1 that correspond to each respective employee and paste them in that employee’s new worksheet. I would then save the file in the appropriate location.
3) If at all possible, as a separate task, I would like to be able to run code that would:
a. Create a new workbook for each of those newly formed employee worksheets
b. To be saved with a file name including the Employee’s name and the current date. The file would be saved in the same folder that I had saved the main file that houses each of the employee’s worksheets.
c. Then, for each newly created workbook, an MS Outlook (2010) email would open which would attach the file corresponding to the employee and populate the employee’s email address based on the value of cell B2 of their workbook. The email would be left open for me to click send independent of the code which would move on to the next employee. I.E. – I could let all of the emails with attachments be created so I can review and hit send on each of them.

I know this is a long request but I’m having issues so any and all help is greatly appreciated. Thanks very much!

Tyler