K
Kanmi
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
")
Lastrow = .Range("D" & Rows.Count).End(xlUp).Row
.Range("D1" & 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.
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
")Lastrow = .Range("D" & Rows.Count).End(xlUp).Row
.Range("D1
" & 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.