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/

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/

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/