VBA Match content from Sheet 1Range A:A from multiple sheets if match is found copy from all sheets b2 values and paste in C2

[Demer]

Hello I need some help please I'm not very good with VBA's. I have a workbook that has multiple sheets and new sheets will be added Daily. I'm trying to take the value from Sheet 1 Range A2-A10' Cell value and search for those values in any sheet excluding the index and Data Sheet . If that value is found in B2 in any of those other sheets then copy C2, F2, G2, and M2 Value on those sheet and paste those values on Sheet1 B, C, D, & E adjacent to the particular value in column A:A

 

[mohadin]

Hi
Try

Sub test()
    Dim a, i&
    Dim dic As Object
    a = Sheets("sheet1").Range("A2:E" & Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row)
    Set dic = CreateObject("scripting.dictionary")
    For i = 1 To UBound(a)
        If a(i, 1) <> 0 Then If Not dic.exists(a(i, 1)) Then dic.Add a(i, 1), ""
    Next
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Sheet1" And Sheets(i).Name <> "index" And Sheets(i).Name <> "Data Sheet" Then
            With Sheets(i)
                If dic.exists(.Range("B2").Value) Then dic.Item(.Range("B2").Value) = _
                   .Range("C2").Value & "|" & .Range("F2").Value & "|" & .Range("G2").Value & "|" & .Range("M2").Value
            End With
        End If
    Next
    With Sheets("Sheet1").Range("B2").Resize(dic.Count)
        .Resize(, 4).ClearContents
        .Value = Application.Transpose(dic.items)
        .TextToColumns Range("B2"), OtherChar:="|", FieldInfo:=Array(Array(4, 1))
    End With
End Sub

 

[Demer]

Hi This VBA works but it concatenates the 4 values into 1 cell in column B next to the referencing value. Is it possible to have those value be independent set into each cell value. Please see image

Names	Header	Header	Header	Header
Test
Test2	84|0|4|0
name3
name4
name5
name6
name7
name8
name9

 

[Demer]

Hello I noticed if I have test on another sheet it does not collect those cells and if there is no match to anything is column A it throws an error

 

[mohadin]

Hi
Replace

 .TextToColumns Range("B2"), OtherChar:="|", FieldInfo:=Array(Array(4, 1))

with

  .TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, Other:=True, OtherChar:="|", FieldInfo:=Array(4, 1)

 

[Demer]

Thanks that worked out great. I really appreciate your help.

 

[mohadin]

You are very welcome
And thankyou for the feedback
Be happy and safe