Search Unopen sheet for value, change color of line, copy line, Paste here.

[jeffcoleky]

Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them. Maybe you can help us?

What we are trying to accomplish is this...
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:\dropbox\returned.xlsm, presses the macro button, and it should do the following:

1. Opens and Searches "Sheet1", "column S", in the file c:\dropbox\master1.xlsm, and finds the LAST instance of the address typed.
2. Selects that entire row
3. Copies the entire row.
4. Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.
5. In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".
6. closes master1.xlsm and saves changes.

The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.

Is there a Guru out there that can help us with a macro for this please?

Other notes:

• I'm open to more efficient steps than this if you have them.
• There are approx 5,000 records to search through in master1.xlsx at any given time.

-Jeff

 

[patel45]

attach a link to 2 small sample files for testing

 

[jeffcoleky]

attach a link to 2 small sample files for testing

Certainly! Here are both sample files for testing. I greatly appreciate this help.
Master1.xlsm
returned.xlsm

 

[jeffcoleky]

It's been two weeks with no response. Should I break my post up into smaller pieces in order to get a response?

 

[patel45]

in the file c:\dropbox\master1.xlsm, the LAST instance of the address typed is on row 5, the gray row is number 3, then I'm confused

 

[jeffcoleky]

Regarding this step:

Opens and Searches "Sheet1", "column S", in the file c:\dropbox\master1.xlsm, and finds the LAST instance of the address typed.

What I mean by this is this: If there are two results of that address on master1.xlsm, then it pics the one further down the sheet. However, i see how that's confusing. Instead, the macro should search for ALL instances of the address, not just the last one. If there are two instances of the address found on master1.xlsm then for each address found, it should copy the row to returned.xlsm and highlight the row on master1.xlsm. Does that clarify things?

Thank you SO MUCH for your help.