Formula/VBA to compare value in row/column in another sheet. If found, copy specific rows to sheet

[malfisint]

Hello,
I am new to VBA and need some help on creating code or using a formula to look at the current sheet A column's rows and look in a different spreadsheet for that same value.
If that value exists, then copy specific row data from the different spreadsheet to the current spreadsheet. For example, Maint Validation sheet has employee id's in columns A2, A3, A4, etc. I would like for those values to be looked at in Sheet Employee Data. If the value is found, the copy specific rows from Sheet Employee Data sheet to Maint Validation sheet.
Is there a formula that can do this? If not has anyone used code to do this? Can you share your code and/or
suggest some code? Any help would be greatly appreciated!!

TIA,
Linda

 

[KennyGreens]

Hi, when you are looking in the Sheet Employee Data sheet, could the compared value be anywhere in the sheet, in a specific column or range of cells? It would help to know that to help make the code.

 

[malfisint]

It would be in a specific column. For example it would be in column W on the Shee Employee Data sheet

 

[malfisint]

Do you need any additional information?

 

[malfisint]

Does anyone have any suggestions?

 

[JackDanIce]

Try:

Sub M1()

    Dim arr()   As Variant
    Dim dic     As Object: Set dic = CreateObject("Scripting.Dictionary")
    Dim x       As Long
    Dim y       As Long
    
    With sheets("Employee Data")
        x = .Cells(.Rows.Count, 23).End(xlUp).Row
        y = .Cells.Find(What:="*", after:=.Cells(1, 1), LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column
        arr = .Cells(2, 1).Resize(x - 1, y).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 23)) = x + 1
    Next x
    
    Application.ScreenUpdating = False
    
    With sheets("Maint Validation")
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr = .Cells(2, 1).Resize(x - 1).Value
        If y < (.Columns.Count * 0.5) Then
            For x = LBound(arr, 1) To UBound(arr, 1)
                .Cells(x + 1, 2).Resize(, y).Value = sheets("Employee Data").Cells(dic(arr(x, 1)), 1).Resize(, y).Value
            Next x
        End If
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr
    Set dic = Nothing
    
End Sub