Better method to distribute 40 worksheet to individuals

[tsunami1977]

Hello!

I have a master file where we track activities of the entire US region. In this file, we have the US data which then feeds into 40 worksheets (one sheet for each individual sales rep). Each week I need to distribute the 40 worksheets to each individual but only his/her own data.

Right now I save the master file as 40 individual files and then delete all the other worksheets before sending it out.

Other than saving the file 40 times, is there an easier way to do this?

Any suggestion would be helpful.

Thank you!

 

[Norie]

Well you could use code to create a new workbook for each worksheet.

Something like this perhaps.

Dim wb As Workbook
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
     ws.Copy
     Set wb = ActiveWorkbook
     wb.SaveAs ws.Name & Format(Date, "mmmyy")
     wb.Close
Next

 

[juster21]

I've done this before for 3 people but it should work the same for 40.
I assigned each sheet the name of the Sales Rep. Then set up an emailing Sub where I list all Sales Rep names and their email addresses. Using a case statement:

Case Rep1
      .Sheet EmailTo:Rep1
Case Rep2
      .Sheet EmailTo:Rep2

etc.

This is the idea. If I can find the code I will post for you.