Excel VBA find account from active and match and paste it in another workbook

[nicatech]

Good morning everyone,

Not sure what technique is best to use - Find, Index/Match or even treat excel as a DB and use ADO sql statement.

I have two workbooks that I work on a monthly basis to finalized "Account Monthly Returns".
I have the following two workbooks:
wkb1 - Source (ActiveWorkbook)
wkb2 - Destination

So if I am working on finalizing February month-end returns on wkb1, once the account is FINAL for the month period, my next step is to archive the account's monthly return to wkb2.

wkb1 is validating book.

wkb2 is the archive and is set up as a Table/Matrix format- has all the Account Codes in Column A and all the Month-Periods as headers in row 2 with month header starting with Jan in Column E and ends with Dec in Column P.

What I would like to do with the macro is to send the Account's Monthly Return from wkb1 once the account status is FINAL and archive it to wkb2 by Account and the corresponding Month Period.

wkb1 - ws set-up: (see screen shot)
Account Code = Column D = AMA2
Month Period = Column E = 2/29/2016
Account Status = Column N = Final
Account Returns = Column L = 0.10


wkb2 - ws set-upsee screen shot)
Account Codes = Column A
Account Name = Column C
Headers on Row 2:
JAN = Column D
FEB = Column E
MAR = Column F
.
.
DEC = Column T


The Code would translate like this:

Sub Archive_Return ()

From the Active wb1/ws
If Column N (Account Status) = "Final" Then
Copy the Return in (Column L) for the Account Code in (Column D) and for the Month Period in (Column E).

(I want the macro to do it on a "Per Account" basis instead of "Per Range" for each accounts status FINAL - so each account row will have a button to process)

Then

With the copy Account Return from wkb1

Find the Account Code in wkb2 Column A, once Account Code is matched.
Paste Special Value the Account Return to the corresponding Month Period (Column D (Jan) to Column T (Dec) in wb2/ws.

Save Archive file.

End Sub

Hope this helps explain the code.

Thanks in advance.

I have this code where it copies the Return only to wkb2.
What I am missing is -how to Find the wkb1 Account Code in wkb2, once matched, paste it along the corresponding Column Month Period.

Sub ArchiveToTR2016()

Application.ScreenUpdating = False

Dim wsI As Worksheet, wsO As Worksheet
Dim wkbO As Workbook, wkbI As Workbook
Dim LastRow As Long, i As Long, j As Long
Dim LastRowIF As Long
Dim LastColumn As Long, a As Long, b As Long
Dim LastRowO As Long, x As Long, y As Long
Dim LastColumnO As Long, c As Long, d As Long
Dim rngI As Range
Dim rngO As Range

Dim RptDateDest As String
Dim FileNameDest As String
Dim DpathDest As String


'''***The Source ActiveWorkbook

    Set wsI = Sheets("All Results")
    'Set rngI = Range(Cells(2, LastColumn), Cells(LastRow, LastColumn))
    Set rngI = wsI.Range("S:X")
      
                                       
    'Last Row in a Column. Row need to start in row 2
    LastRow = wsI.Cells(Rows.Count, "X").End(xlUp).Row
    
    'Last Column in a Row.
    LastColumn = wsI.Cells(5, Columns.Count).End(xlToLeft).Column
       
    'Row start
    j = 2
    
    With wsI
    
        For i = 1 To LastRow
                        
            If Range("N" & i).Value = "Final" Then
            
                wsI.Range("X" & i).Copy
                
       
                
    '''***The Destination Workbook
    ''''Once you copy the Return from the Source - Find and Match the Account Code and Paste it to Destination
    ''to corresponding Column Month-End
                          
            Set wkbO = Workbooks.Open("U:\Final Returns\2016\Feb\Total Returns 2016.xlsm")
            Set wsO = Sheets("Total Returns")
            Set rngO = Range(Cells(2, LastColumn), Cells(LastRow, LastColumn))
            Set rngO = wsO.Range("E:E")
            
            LastRowIF = wsI.Cells(Rows.Count, "S").End(xlUp).Row
                                          
            'Last Row in a Column.
            LastRowO = wsO.Cells(Rows.Count, "A").End(xlUp).Row
            'Last Column in a Row.
            LastColumnO = wsO.Cells(1, Columns.Count).End(xlToLeft).Column
                                   
            wsO.Activate
                
                 'If Account Code is Matched with that of wkbI.wsI then Paste Return and Color Filled the Cell
                                                                                               
               wsO.Range("E" & i).PasteSpecial Paste:=xlPasteValues
               wsO.Range("E" & i).Interior.ColorIndex = 34
                                                                                            
                           
            j = j + 1
                       
                                           
           
         End If
    
    Next i
  
End With
 
End Sub