finding partial string into another workbook, then do several cell copy when found

[Alternsti]

Hi everyone,

I want to update several lines/columns into a worksheet (ex. WStarget) from another one (ex. WSmaster).

1st: For every lines into WBmaster (ex. L2 to L250) I must find where this "partial string" is located inside WStarget (ex. X2-X1000).
2nd: When I found a match inside WStarget, I want to paste specific columns from WSmaster inside specific columns inside WStarget.


I'm able to find where the "partial string" is located but I'm having problems when trying to fill several columns using Index Match. I wonder if it'll be simpler to use VBA instead.
(Inside WStarget)
=INDEX($A$2:$AA$284;EQUIV("*"&MSmaster!L2&"*";$AA$2:$AA$284;0);2)
(Inside WSmaster)
=INDEX('WStarget!$A$2:$AA$284;EQUIV("*"&L2&"*";'WStarget$AA$2:$AA$284;0);2)

Have yourself a Merry Christmas and Happy New Year btw!



VBA code I'm working on right now:

Const w1 As String = "WBtarget-WStarget (2021-12-17).xlsx"
Const w2 As String = "WMmaster.xlsx"

Sub LiveData_pdate()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim cl As Range, ws1Rng As Range, ws2Rng As Range, aCell As Range
    Dim lRowW1 As Long, lRowW2 As Long

    '~~> Define your workbook and worksheets here
    Set wb1 = Workbooks(w1)
    Set ws1 = wb1.Sheets(1)
    Set wb2 = Workbooks(w2)
    Set ws2 = wb2.Sheets(1)

    '~~> Work with First workbook to get last row and define your range
    With ws1
        lRowW1 = .Range("E" & .Rows.Count).End(xlUp).Row
        Set ws1Rng = .Range("E2:E" & lRowW1)
    End With

    '~~> Work with Second workbook to get last row and define your range
    With ws2
        .Range("A1", "D1").EntireColumn.Hidden = True

        lRowW2 = .Range("E" & .Rows.Count).End(xlUp).Row
        Set ws2Rng = .Range("E2:E" & lRowW2)

        For Each cl In ws2Rng
            '~~> Do the find
            Set aCell = ws1Rng.Find(what:=cl.Value, LookIn:=xlValues)

            '~~> Check if found or not. This is required else you will
            '~~> get an error if no match found
            If Not aCell Is Nothing Then
                '
                '~~> Do what ever you want here
                '
            End If
        Next
    End With
End Sub

[Dave]

Hi Alternsti. Seems like your not having much luck with this one. U need to indicate what columns (or range) that need to be transferred from the Wstarget wb and where U want them placed in the Master wb. HTH. Dave

[Alternsti]

Hi Dave, I'll give it a try during my vacation.

Thanks and have yourself a Merry Christmas and Happy New Year.