Populate worksheets fro a Master List

P

Patrick C. Simonds

I have a Worksheet which has a master list of all employees. There are 3
columns of information (First Name, Last Name @ Department). I want to
create a worksheet for each of the 3 departments and have each worksheet
populated with the information for that department only. The reason I
created the Master List is that people can move between the departments so I
was hoping to update the Master List and then have the Department
worksheets automatically update.

The 3 departments are PT, FR and OF. Is there any code (say an On Change
event) which would look at the Department Code for each line on the Master
List and Copy the information (First Name, Last Name and Department) to the
appropriate worksheet (worksheets are named Master, PT, FR and OF).
 
L

Lorne

Patrick C. Simonds said:
I have a Worksheet which has a master list of all employees. There are 3
columns of information (First Name, Last Name @ Department). I want to
create a worksheet for each of the 3 departments and have each worksheet
populated with the information for that department only. The reason I
created the Master List is that people can move between the departments so
I was hoping to update the Master List and then have the Department
worksheets automatically update.

The 3 departments are PT, FR and OF. Is there any code (say an On Change
event) which would look at the Department Code for each line on the Master
List and Copy the information (First Name, Last Name and Department) to
the appropriate worksheet (worksheets are named Master, PT, FR and OF).

Right click on the tab for the sheet with the master list, select view code
from the menu and write something like this (it will run the code after the
if statement if any cell in the stated range changes):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ws_exit:
If Application.Intersect(Target, Range("A2:C100")) Is Nothing Then Exit
Sub

some code to copy the 3 columns to other sheets ....
......
.....

ws_exit:
code to handle any errors ....
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top