Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AUTOMATIC DATA ENTRY
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 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AUTOMATIC DATA ENTRY
On Jul 1, 11:37*am, Kanmi wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AUTOMATIC DATA ENTRY
This doesn't sound too hard, Kanmi. What have you tried so far? I mean, you say you need to do this but you don't say what part of it you need help for. Have you started to write a program to do it, and part of it isn't working right? Or do you not know how to start? --- "Kanmi" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AUTOMATIC DATA ENTRY
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: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 "Bob Bridges" wrote: This doesn't sound too hard, Kanmi. What have you tried so far? I mean, you say you need to do this but you don't say what part of it you need help for. Have you started to write a program to do it, and part of it isn't working right? Or do you not know how to start? --- "Kanmi" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AUTOMATIC DATA ENTRY
File Location: http://www.4shared.com/file/11638244...6f/source.html http://www.4shared.com/file/11638259...stination.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 "Joel" wrote: 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: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 "Bob Bridges" wrote: This doesn't sound too hard, Kanmi. What have you tried so far? I mean, you say you need to do this but you don't say what part of it you need help for. Have you started to write a program to do it, and part of it isn't working right? Or do you not know how to start? --- "Kanmi" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatic data entry | Excel Programming | |||
Automatic Data Entry | Excel Discussion (Misc queries) | |||
Automatic Data Entry | Excel Discussion (Misc queries) | |||
Automatic data entry | Excel Discussion (Misc queries) | |||
Programming automatic data entry? | Excel Programming |