Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB CODE ERROR
Sub ArchiveData() Dim NextCol As Long 'Check if destination workbook is open already, open it if necessary On Error Resume Next Workbooks("destination.xls").Activate If Err < 0 Then Workbooks.Open "destionation.xls" 'Copy data NextCol = Workbooks("destination.xls").Sheets("WPS Detail Dates").Range("A2").Value Windows("source.xls").Activate Columns("A:D").Copy 'Paste data to new column and insert archive date Workbooks("destination.xls").Sheets("WPS Detail Dates").Activate Columns(NextCol).PasteSpecial xlPasteAll Application.CutCopyMode = True Cells(2, NextCol) = Date Columns(NextCol).HorizontalAlignment = xlCenter Columns(NextCol).AutoFit 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" 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. I want to use above code or if any other can work. Please your taught and help will be much more welcome and appreciate your time. Thank you and look forward to hear from you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
OnTime code error "can't execute code in break mode" | Excel Programming | |||
Error in Excel VBA Code (Error 91) | Excel Programming | |||
How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |