VBA-help <3

[eekka]

I am trying to connect two files (one to be used as template-source file) and second one as target file. Aim is to populate target file with data from source file that is named differently than in the source file. However this code doesn't work and I don't know where I have made a mistake. Any help is highly appreciated! Also it is important to note every single time when source data is changed, data should be pasted in the next row.

Sub SearchAndFillData()
    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim targetWorksheet As Worksheet
    Dim sourceRICColumn As Range
    Dim targetHeader As Range
    Dim sourceCell As Range
    Dim targetCell As Range

    Set sourceWorkbook = Workbooks.Open("C:\Path\To\x.xlsx")
    Set targetWorkbook = Workbooks.Open("C:\Path\To\ TEST-3.xlsx")

   
    Set sourceWorksheet = sourceWorkbook.Sheets("Sheet1")
    Set targetWorksheet = targetWorkbook.Sheets("Sheet1")

 
    Set sourceRICColumn = sourceWorksheet.Range("H2:H" & sourceWorksheet.Cells(sourceWorksheet.Rows.Count, "H").End(xlUp).Row)

   
    Set targetHeader = targetWorksheet.Range("A15:H15")

  
    For Each targetCell In targetHeader.Cells
 
        Set sourceCell = sourceRICColumn.Find(targetCell.Value, LookIn:=xlValues, lookat:=xlWhole)

       
        If Not sourceCell Is Nothing Then
           
            targetCell.Offset(1, 0).Value = "=CENT"
            Select Case targetCell.Value
                Case "RIC"
                    targetCell.Offset(1, 1).Value = sourceWorksheet.Range("H2").Value
                Case "OFFCL_CODE"
                    targetCell.Offset(1, 1).Value = sourceWorksheet.Range("B" & sourceCell.Row).Value
                Case "DISPLY_NAME"
                    targetCell.Offset(1, 2).Value = "#IGNORE"
                Case "MATUR_DATE"
                    targetCell.Offset(1, 3).Value = sourceWorksheet.Range("AP" & sourceCell.Row).Value
                Case "CURRENCY"
                    targetCell.Offset(1, 3).Value = sourceWorksheet.Range("Q" & sourceWorksheet.Range("Q2").Row).Value
                Case "BKGD_REF"
                    targetCell.Offset(1, 5).Value = sourceWorksheet.Range("B02").Value
                Case "OFF_CD_IND"
                    targetCell.Offset(1, 6).Value = "ISN"
                Case "OFFC_CODE2"
                    targetCell.Offset(1, 7).Value = sourceWorksheet.Range("I2").Value
            End Select
        End If
    Next targetCell


    MsgBox "Data copied successfully!", vbInformation
End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.