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 :-) 


Sub Copy_To_Another_Sheet_1()
    
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim Wk As String
Dim RefN As String
Dim PasteTo As Range
Dim FirstAddress As String
Dim Rng As Range
Dim Rcount As Long
Dim Q As Long
    
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

Set Wb1 = Workbooks("Book1.xlsm")
RefN = Wb1.Sheets("Screen").Range("K18").Value
    If RefN = "" Then
       MsgBox "nothing to search for"
       End
    End If
Set PasteTo = Wb1.Sheets("COPY").Range("A37:U44")
Set Wb2 = Workbooks.Open("\\...OMITTED...\Book2.xlsm", Password:="pass", _
            WriteResPassword:="pass", IgnoreReadOnlyRecommended:=True, UpdateLinks:=True)
Wk = Wb1.Sheets("Screen").Range("H18").Value

With Wb2.Sheets(Wk).Range("A:A")

        Rcount = 0
            
            'I need this to ONLY find the LAST occurrence
            
            Set Rng = .Find(What:=RefN, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                FirstAddress = Rng.Address
                Do
                
                Rcount = Rcount + 1
                    
'NEED SOMETHING IN HERE TO OFFSET/RESIZE/SELECT THE CELL WHERE THE MATCH HAS BEEN FOIND
'AND THE NEXT 20 COLUMNS AND 8 ROWS!!!

                    Rng.Copy PasteTo
                    
                    Set Rng = .FindNext(Rng)
                    
                Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
            Else
            MsgBox "Reference number not found"
            End If
        
    End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub