Better method to distribute 40 worksheet to individuals

tsunami1977

Board Regular
Joined
Jan 24, 2005
Messages
58
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-known Member
Joined
Apr 28, 2004
Messages
75,288
Well you could use code to create a new workbook for each worksheet.

Something like this perhaps.
Code:
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

Well-known Member
Joined
Jun 3, 2005
Messages
867
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:

Code:
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.
 

Forum statistics

Threads
1,078,473
Messages
5,340,544
Members
399,383
Latest member
rahmanab001

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top