AUTOMATIC DATA ENTRY

[Kanmi]

Master!. i know this is hard to understand what I'm trying to do but I will
appreciate your taught and help to solve this. My humble pleasure to you all.

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"
A B C D
A1 EMPLOYEE ID COURSE NAME MANAGER
Kim Bell 002 Sales Training Brian
Kim Bell 002 Safety Training Brian
Lee Paul 003 Light Training Mark
Lee Paul 003 Sales Training Mark.
"DESTINATION.XLS"

B C D

B11 MANAGER EMPLOYEE ID
BRIAN Kim Bell 002
MARK Lee Paul 003

I mean the names should appear on the destination.xls automatically
according to thier MANAGER and each name should appear once on the
destination.xls "sheet1" even if new staff data pulled from database to
source.xls.
The COURSE NAME ROW is neccesary but it can be scrap out if it will prevent
us to achieve what we want.

Please your taught and help will be much more welcome and appreciate your
time. Thank you and look forward to hear from you.

Was this post helpful ?

 

[Matthew Herbert]

Master!. i know this is hard to understand what I'm trying to do but I will
appreciate your taught and help to solve this. My humble pleasure to you all.

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"
           A                    B            C                         D
A1    EMPLOYEE          ID        COURSE NAME     MANAGER
       Kim Bell              002      Sales Training         Brian
       Kim Bell              002      Safety Training        Brian
       Lee Paul             003      Light Training          Mark
       Lee Paul             003      Sales Training         Mark.
"DESTINATION.XLS"

            B                    C                D                          

B11   MANAGER     EMPLOYEE         ID
        BRIAN            Kim Bell          002
        MARK             Lee Paul          003

I mean the names should appear on the destination.xls automatically
according to thier MANAGER and each name should appear once on the
destination.xls "sheet1" even if new staff data pulled from database to
source.xls.  
The COURSE NAME ROW is neccesary but it can be scrap out if it will prevent
us to achieve what we want.    

Please your taught and help will be much more welcome and appreciate your
time. Thank you and look forward to  hear from you.

 Was this post helpful ?

Kanmi,

Have you tried a pivot table or data filter yet?

Best,

Matthew Herbert

 

[Joel]

Try this code. Change Sheet name as required for source workbook. I create
a new sheet in the destination workbook.


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

 

[Kanmi]

File Location:
http://www.4shared.com/file/116382449/6ab0a56f/source.html
http://www.4shared.com/file/116382594/a06dcda8/destination.html

Please just edit this. is the only one working. edit the session not to
create new sheet. let it insert the information to destination.xls sheet name
"WPS Detail Dates".
code below:
Sub CreateMaster()

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

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

With DestSht

SourceSht.Columns("C:C").Copy _
Destination:=.Columns("C:C")
Lastrow = .Range("C" & Rows.Count).End(xlUp).Row

.Range("C1:C" & Lastrow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("B1"), _
Unique:=True

'delete temporay column C
.Columns("C").Delete

.Range("A1") = "SALES"
.Range("B1") = "ID"
.Range("C1") = "Employee"
.Range("D1") = "Hire Date"
.Range("E1") = "Manager"
.Range("F1") = "Reg"
.Range("G1") = "Title"

Lastrow = .Range("B" & Rows.Count).End(xlUp).Row
For RowCount = 2 To Lastrow
ID = .Range("B" & RowCount)

With SourceSht
Set c = .Columns("C").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find ID : " & ID)
Else
Sales = "N/A"
Reg = "N/A"
Employee = .Range("A" & c.Row)
HireDate = .Range("D" & c.Row)
Title = .Range("E" & c.Row)
Manager = .Range("G" & c.Row)
End If
End With

If Not c Is Nothing Then
.Range("A" & RowCount) = Sales
.Range("C" & RowCount) = Employee
.Range("D" & RowCount) = HireDate
.Range("E" & RowCount) = Manager
.Range("F" & RowCount) = Reg
.Range("G" & RowCount) = Title
Else
MsgBox ("Error : Count not find ID : " & ID)
End If
Next RowCount
End With

End Sub

Sub UpdateMaster()

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

Set Dest = Workbooks("Destination.xls")
'Set Dest = ThisWorkbook
Set DestSht = Dest.Sheets("Master")
With DestSht
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = Lastrow + 1
End With
With SourceSht

Lastrow = .Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 2 To Lastrow
Sales = "N/A"
Reg = "N/A"
ID = .Range("C" & RowCount)
Employee = .Range("A" & RowCount)
HireDate = .Range("D" & RowCount)
Title = .Range("E" & RowCount)
Manager = .Range("G" & RowCount)

With DestSht
Set c = .Columns("B").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
DataRow = NewRow
NewRow = NewRow + 1
.Range("B" & DataRow) = ID
Else
DataRow = c.Row
End If

.Range("A" & DataRow) = Sales
.Range("C" & DataRow) = Employee
.Range("D" & DataRow) = HireDate
.Range("E" & DataRow) = Manager
.Range("F" & DataRow) = Reg
.Range("G" & DataRow) = Title
End With
Next RowCount
End With

End Sub