Split Excel sheets to multiple Workbooks based on Criteria in Cell A1

I have a file with many sheets, each sheet is relevant to a specific sales person. However, each salesperson always has 2 or more sheets. The sales person name is in cell A1 on every sheet. I am looking for a macro that would cycle through all the tabs in the worksheet and save a new file for each sales employee with their relevant sheet. The file name should be the sales employee's name

If possible, would also be great to automatically email each file to the relevant salesperson based on a list (or it can also always be in a cell, B1 for instance)

Many thanks

Are there any non-employee sheets (summary sheets etc) in the workbook
ie any sheets that should not be saved

just trying to work out is how to eliminate non-employee sheets from this procedure?
- could A1 be left blank for ALL non-employee sheets?
- else need a list of the names of any the non-employee sheets names
thanks

Hi,

There are no additional tabs in the file. All the tabs have a name in A1

This should give you what you want.
-temporary sheet created to collate and sort sheet summary by employee
-array of sheets by employee created
-separate file for each employee created containing all sheets for that employee based on values passed from array
-temporary file delete

All you need do is
-include this macro in the employee file
-amend the emplPath to wherever the files should be saved
-note that any previous file with same name in that location is overwritten

Please Login or Register  to view this content.

Amazing! worked like a charm! Many thanks
What do I need to add in to send each file to the sales rep automatically. The email address could be located in an adjacent cell, say B1. Would be from an outlook account

Please mark this thread as solved and open up new thread for your new question
You are starting again, so include a full explanation of your problem - do not assume that anyone has read this thread.
thanks