VBA to search for value in all sheets then copy adjacent range until first cell to the master sheet

[okinawa]

Hello

i have column "A" each cell in it contains value, i want to look for each value in all sheets in the workbook, when find the matching value, then copy all what's in the left of that cell from the found sheet to the master sheet taking in consideration the bellow points :

Found sheet the range order i want to copy is : E-D-C-B-A (From found cell going left until the beginning of row)
Master Sheet the copied range i want past in this order : A-B-C-D-E (A will be in column "A" the rest will be pasted to the right and go on

i tried with this peace of code but i keep getting errors .
this code does have loop command yet and still struggling with copy all to the left .

Sub Plan_Rout()
   Dim Fnd As Range, A1 As Range
   Dim Lr As Long
   
   
   With Sheets("sheet1")
      For Each A1 In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Set Fnd = Sheets("Sheet2").Range("A1:Z50").Find(A1.Value, , xlFormulas, xlWhole, xlByRows, xlPrevious, False, , False)

         If Not Fnd Is Nothing Then A1.Offset(, 1).Value = Fnd.Offset(, -1).Value
         If Not Fnd Is Nothing Then A1.Offset(, 2).Value = Fnd.Offset(, -2).Value
         'if i add another with offset 3 i get error'
         Next A1
   End With
End Sub

 

[ouvay]

Hi,

so I don't obviously have your data samples to work with, but I did copy paste your code into and even with the extra offset 3, I didn't get any errors..
One obvious thing I noticed was that your code doesn't loop through all the sheets in your workbook?
was that intentional? or have you just shared it as such for readability on the forum?

 

[Alex Blakenburg]

I expect that you got an error because the value found was in Column C. Offset(,-3) takes you to Column 0 which doesn't exist.

Fleshing out what you already have gets me to the below.
I am not sure that is what you need though, since you are putting the values next to the Lookup value it would keep overwriting it if it finds it in later worksheets. The code below exits the loop the first time it finds it, so it will show the first result only.

Sub Plan_Rout()
    Dim Fnd As Range, A1 As Range
    Dim Lr As Long
    Dim fndColNo As Long, iCol As Long
    Dim wsCurrent As Worksheet
    
    With Sheets("sheet1")
        
        For Each A1 In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            For Each wsCurrent In Worksheets
                If wsCurrent.Name <> "Sheet1" Then
                    Set Fnd = wsCurrent.Range("A1:Z50").Find(A1.Value, , xlFormulas, xlWhole, xlByRows, xlPrevious, False, , False)
                    If Not Fnd Is Nothing Then
                        fndColNo = Fnd.Column
                        For iCol = 1 To fndColNo - 1
                            If Not Fnd Is Nothing Then A1.Offset(, iCol).Value = Fnd.Offset(, -iCol).Value
                        Next iCol
                        Exit For                ' If found in a worksheet, do not keep looking in more worksheets
                    End If
                End If
            Next wsCurrent
        Next A1

    End With
End Sub