Reply to thread

Message

[QUOTE="Kanmi, post: 6399495"]
Sub CreateMaster()

Set Source = Workbooks("Source.xls")
Set SourceSht = Source.Sheets("Sheet1")

Set Dest = Workbooks("Destination.xls")
'create new worksheet
With Dest
Set DestSht = .Sheets.Add(after:=.Sheets(.Sheets.Count))
End With

With DestSht
SourceSht.Columns("A:A").Copy _
Destination:=.Columns("D:D")
Lastrow = .Range("D" & Rows.Count).End(xlUp).Row

.Range("D1:D" & Lastrow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("C11"), _
Unique:=True

'delete temporay column D
.Columns("D").Delete
.Range("B11") = "MANAGER"
.Range("D11") = "ID"

Lastrow = .Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 12 To Lastrow
Employee = .Range("C" & RowCount)

With SourceSht
Set c = .Columns("A").Find(what:=Employee, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find employee : " & Employee)
Else
Manager = .Range("D" & c.Row)
ID = .Range("B" & c.Row)
End If
End With

If Not c Is Nothing Then
.Range("B" & RowCount) = Manager
.Range("D" & RowCount) = ID
End If
Next RowCount
End With

End Sub
-----------------------------------------------------------
I have a large two workbook  (destination.xls and source.xls), that track
training of employee. The source.xls is link to the database such a way that
whenever i open, it  pull Automatic update from the database. Manager has
list of employee under him that have undergone the training and these names
sometimes occur more than one time because they have two or more training.
I am trying to set up way that after update pulled by source.xls from the
database should Automatically copies to Destination.xls on a particular rows
and also only pick one name at a time if they appear more than one time"maybe
pick the first occured of each name" and cordinate the name by Manager.

For example

SOURCE.XLS
----------------  -A1:G1
A              B            C             D              E         F
G
Employee	Login ID	 ID        Hire Date     Title     Email     Manager
kim Belly    kima         001      06/21/01       MD      kim@       Jen
kim Belly    kima         001      06/21/01       MD      kim@       Jen
Fue Lee     leeo          002      02/07/02       SALES  leeo@     Mark
Ben Jud     bee           003      02/07/02       MD       bee@      JEN
Yao yu      yao            004     02/25/05        MA      yao@      Tim-
Yao yu      yao            004     02/25/05        MA      yao@      Tim
Yao yu      yao            004     02/25/05        MA      yao@      Tim

DESTINATION.XLS
----------------------
A          B          C             D               E               F
G
SALES     ID     Employee   Hire Date     Manager     Reg       Title
N/A       001    KIM BELLY   06/21/01    JEN            N/A         MD
N/A       003    BEN JUD      02/07/02    JEN            N/A        MD
N/A       002    FUE LEE      02/07/02    MARK         N/A        SALES
N/A       004    YAO YO      02/25/05    TIM            N/A        MA

This is how the page appeared on each workbook and i have arrange
destination.xls the way i want it to work. I want destination.xls
automatically pulled update from source.xls and list them according to the
manager and even if new students were added then should automatically appear
under it manager. I got the VB CODE ABOVE TO EDIT AND SEE OTHER WAYS TO
ACHIEVE THIS.

I know this might hard to go through but i will appreciate any advice or
help because it all chanllenge. Thanks and God bless you.
[/QUOTE]

Verification