Hi Guys,

In short, I need to search for a value in column A of a sheet in another workbook and copy that block of cells back to the first workbook.

The code is in Wb1. Search for a Reference number in column 'A' (only) of a specific (chosen) worksheet of Wb2. The specific worksheet name is in Wb1.Sheets("Screen").Range("H18"). The reference number I'm searching for is in Wb1.Sheets("Screen").Range("K18").

If the Reference number isn't found, I need a message box to say "the reference number isn't found and to check the reference number the user has entered (in K18) and try again."

Otherwise, I need to find the LAST occurrence of the Reference number, then copy/paste (or otherwise) the range of cells from A* to U*+7 (the * being the row number of the match e.g. A15:U22) to Wb1.Sheets("Copy").Range("A37:U44"), write the word "DONE" in Wb2.Sheet.V8 then save and close Wb2 and the code should end.

However, if the match already has "DONE" in column V*, I need a message to ask if the user would like to continue to paste the range A* to U*+7 to Wb1.

The code I have so far (Frankensteined from other's code!) is below. I'm not sure I need everything in there but I know I need additional code to make it work how I want. It currently searches though column A of the correct sheet but returns EVERY match but pastes the same matching ref into every cell of "A37:U44".

(I've also posted this on Mr.Excel (http://www.mrexcel.com/forum/excel-q...l#post40119250) Thanks)

Thanks guys,

ExcelNat :-) 


Please Login or Register  to view this content.