Copy Rows by Cell Value, Paste to Newly Created Worksheet, Email Contents

[TylerK_BLL]

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

[TylerK_BLL]

Example Data.xlsx

Here's an attachment with example data. What's in Green is what I have in place and what's in red is what I'm hoping for.

[TylerK_BLL]

Here's the existing code I have:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi, and welcome to the forum.

It's a fairly standard requirement and one I've come across a lot and have written for many clients.

The approach I always adopt is to use an advanced data filter to filter the employees records to a second sheet. In a working sheet I create a list of employee names and then in a macro create a loop that cycles down the list and pops each name in turn into the filter criteria cell.

Once the employee records have been filtered the macro copies the filtered sheet, thus creating a new workbook. The macro names the single sheet in the new workbook and if necessary copies the cells and pasted them back as hard values, plus any other tidying up stuff.

The macro then saves the newly created workbook with the employee name and any other text in the name and closes the workbook so that the loop then moves on to the next name on the list.

[Leith Ross]

Hello TylerK_BLL,

Welcome to the Forum!

You address "new" worksheets quite a bit, but what about the existing sheets? Will new data be added to these and emailed also?

[TylerK_BLL]

Thanks for the warm welcome!

Richard, Thanks for your response. That sounds like a good solution. I'm afraid I wouldn't know where to start.

Leith Ross, Thanks for your response as well. In my mind, the workbook would start as a template file with 2 worksheets - Sheet1 with data that would be pasted manually from another source and Sheet2 that would ultimately have a single instance of each of the employees present in Sheet2. Upon initiating the macro, new worksheets would be created for each employee and those new worksheets would be the only ones that would be emailed - ultimately as separate workbooks.