Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search/copy/paste Macro
Friends,
I am working on an automation procedure that I hope will greatly speed up some work at my office. I would like a bit of help with a macro that I am creating. The Excel spreadsheet I am working on has two sheets. The first sheet is an excel-formatted output of our accounting software. This particular software is for non-profit firms like ours and is not something you would see "off the shelf." This first page contains the names and payroll-check totals of our employes for a given payroll period. We use this data to manually input the values into ACH (Automated ClearingHouse)software given us by our bank. Entering the data into this software allows us to make payroll payments directly into employee bank accounts. This information will change almost daily. The second sheet holds the the name, bank number and bank-account number of each employee. This information will remain the same unless an employee provides us a change in bank account information or employees are added/deleted. What I need is a macro to look at the name of the first employee listed on sheet one, use it to make a match with the correct employee from sheet two, then copy the bank number and account number of that particular employee from sheet two to cells B and C on sheet one next to the correct employee name. The process must then go on to process all of the other names on sheet one in the same way, stopping when no more names are present. Here is how the sheets are configured: Sheet1: A B C D Name Bank Number Acct Number Amount Sheet 2: A B C Name Bank Number Acct Number It should be noted that each payroll run has a different configuration of names. For instance, today I might create one payment each for six of our employees. In two days, I might create a payment for a 100 employees. It is for this reason that there must be a matching of names from both sheets before any copy/paste procedure can be performed. Finally, some employees have more than one bank account. It would be nice to be able to select the appropriate account on the second page before running the process. This would allow the correct account information to be pasted to the first page. This is not the most critical consideration of the above matter, but would save some additional time. I would appreciate any help you can provide. Thank you so much for your help! Mike --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search/copy/paste Macro
You don't need macros
In cell B2 put the following formula: =IF(ISNA(VLOOKUP(A2,Sheet2!A:C,2,FALSE)),"",VLOOKU P(A2,Sheet2!A:C,2,FALSE)) and in cell C2, this formula: =IF(ISNA(VLOOKUP(A2,Sheet2!A:C,3,FALSE)),"",VLOOKU P(A2,Sheet2!A:C,3,FALSE)) Just drag down for as many cells as you need. Regards Trevor "mjwillyone" wrote in message ... Friends, I am working on an automation procedure that I hope will greatly speed up some work at my office. I would like a bit of help with a macro that I am creating. The Excel spreadsheet I am working on has two sheets. The first sheet is an excel-formatted output of our accounting software. This particular software is for non-profit firms like ours and is not something you would see "off the shelf." This first page contains the names and payroll-check totals of our employes for a given payroll period. We use this data to manually input the values into ACH (Automated ClearingHouse)software given us by our bank. Entering the data into this software allows us to make payroll payments directly into employee bank accounts. This information will change almost daily. The second sheet holds the the name, bank number and bank-account number of each employee. This information will remain the same unless an employee provides us a change in bank account information or employees are added/deleted. What I need is a macro to look at the name of the first employee listed on sheet one, use it to make a match with the correct employee from sheet two, then copy the bank number and account number of that particular employee from sheet two to cells B and C on sheet one next to the correct employee name. The process must then go on to process all of the other names on sheet one in the same way, stopping when no more names are present. Here is how the sheets are configured: Sheet1: A B C D Name Bank Number Acct Number Amount Sheet 2: A B C Name Bank Number Acct Number It should be noted that each payroll run has a different configuration of names. For instance, today I might create one payment each for six of our employees. In two days, I might create a payment for a 100 employees. It is for this reason that there must be a matching of names from both sheets before any copy/paste procedure can be performed. Finally, some employees have more than one bank account. It would be nice to be able to select the appropriate account on the second page before running the process. This would allow the correct account information to be pasted to the first page. This is not the most critical consideration of the above matter, but would save some additional time. I would appreciate any help you can provide. Thank you so much for your help! Mike --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search/copy/paste Macro
Trevor,
Thanks for your help ... it worked great! I made a small adjustment to your programming and now I am set! Thanks again - what a HUGE timesaver! Mike --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search, copy and paste through multiple sheets | Excel Discussion (Misc queries) | |||
Search, Copy & Paste Formula | Excel Discussion (Misc queries) | |||
Search, copy and paste help | Excel Discussion (Misc queries) | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
VBA-code for search,copy and paste | Excel Discussion (Misc queries) |