I have a personal project that requires obtaining information from multiple cells in a worksheet and transferring that various information to other worksheets. I have no knowledge of macro, but I know what I need to have done must be be done with a macro.. I seek the help of the Excel gurus to offer me with a code (and hopefully explain how it works). I AM HOPING THE EXPERTS WILL HELP ME WITH THIS CHALLENGE.
Since there is no guarantee the information in a particular row for every instance, I need Excel to look for a string in Column A and then find the contents of the cell beneath (or adjacent to) that string.. and then make a list of that information on a different worksheet.
The information will be manually copied/pasted onto SHEET1 by me before I run the macro.. most of the relevant data will be in Column A and B
1) When the macro is run in SHEET1, I want Excel to look along columns A:B and find the Employee#, the Last name, the First name, the gender, and the date of birth and copy that data on a separate sheet (called LIST1). The next time the macro is run, Excel should copy the new information below the previous entry. Also, it would be good to have Excel notice the MALE or FEMALE in SHEET1 and convert that to "Mr." or "Ms."
2)When the macro is run in SHEET1, I also want Excel to form a similar list on worksheet LIST2. In this worksheet, I need Excel to concatenate the three names of the employee and enter it in one cell; and enter the employee# and the adjacent cell. Similar to LIST1, Excel should copy the new data below the previous entry.
3)In the worksheet FORM1, I want Excel to copy certain information from SHEET1 all concatenated in one cell. I intend to use this to print the address on envelopes. So in FORM1 in one particular cell, Excel should copy the three names; drop down a line; copy STREET1 and STREET2 with a space in between; drop a line; copy the CITY, STATE, and ZIP (with the necessary spaces and comma) or the "Primary Address". When the macro is run again, Excel should simply write the new information over the previous in that same cell.
4) In worksheet FORM2, the function will be similar to FORM1 except that on this worksheet, Excel uses the information found under "Mailing Address" in SHEET1
<tbody>
</tbody>
Since there is no guarantee the information in a particular row for every instance, I need Excel to look for a string in Column A and then find the contents of the cell beneath (or adjacent to) that string.. and then make a list of that information on a different worksheet.
The information will be manually copied/pasted onto SHEET1 by me before I run the macro.. most of the relevant data will be in Column A and B
1) When the macro is run in SHEET1, I want Excel to look along columns A:B and find the Employee#, the Last name, the First name, the gender, and the date of birth and copy that data on a separate sheet (called LIST1). The next time the macro is run, Excel should copy the new information below the previous entry. Also, it would be good to have Excel notice the MALE or FEMALE in SHEET1 and convert that to "Mr." or "Ms."
2)When the macro is run in SHEET1, I also want Excel to form a similar list on worksheet LIST2. In this worksheet, I need Excel to concatenate the three names of the employee and enter it in one cell; and enter the employee# and the adjacent cell. Similar to LIST1, Excel should copy the new data below the previous entry.
3)In the worksheet FORM1, I want Excel to copy certain information from SHEET1 all concatenated in one cell. I intend to use this to print the address on envelopes. So in FORM1 in one particular cell, Excel should copy the three names; drop down a line; copy STREET1 and STREET2 with a space in between; drop a line; copy the CITY, STATE, and ZIP (with the necessary spaces and comma) or the "Primary Address". When the macro is run again, Excel should simply write the new information over the previous in that same cell.
4) In worksheet FORM2, the function will be similar to FORM1 except that on this worksheet, Excel uses the information found under "Mailing Address" in SHEET1
| A | B | C | D | E | F | G | H | |||
| 1 | Employee# | 12345678 | Smith | John | Mr. | 01/02/1982 | ||||
| 2 | 12345678 | |||||||||
| 3 | John Doe Smith | 12345678 | ||||||||
| 4 | Basic Information | |||||||||
| 5 | FIRST NAME | John | Mr. John Doe Smith | |||||||
| 6 | MIDDLE NAME | Doe | 123 Any Street | |||||||
| 7 | LAST NAME | Smith | Apt #111 | |||||||
| 8 | SUFFIX | Jr | Houston, TX 77001 | |||||||
| 15 | DOB | 01/02/1982 | ||||||||
| 18 | SEX | Male | Mr. John Doe Smith | |||||||
| 20 | Physical Address | 456 This Lane | ||||||||
| 21 | Street1 | 123 Any Street | - | |||||||
| 22 | Street2 | Apt #111 | Dallas, TX 77007 | |||||||
| 23 | City | Houston | ||||||||
| 24 | State | TX | ||||||||
| 25 | Zip | 77001 | ||||||||
| 30 | Mailing Address | |||||||||
| 31 | Street1 | 456 This Lane | ||||||||
| 32 | Street2 | |||||||||
| 33 | City | Dallas | ||||||||
| 34 | State | TX | ||||||||
| 35 | ZIP | 77007 |
<tbody>
</tbody>
