Macro help vlookup-> copy row

[Warhammer]

trying match a cell value to another sheet within the workbook (sheet1 cell A1) like a Vlookup but when it matches in the a column of Sheet 3 i want it to copy the row and paste it in A5 on sheet1.
here is what i'm using right now....

Sub copyit()
Dim MyRange, MyRange1 As Range
Sheets("Sheet2").Select 'Change to suit
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet2").Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = "Sheet1!A1" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Copy
End If

Sheets("Sheet1").Select 'Change to where you want it
Range("A5").Select 'Change to where you want it
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] copyit()
    
    [color=darkblue]Dim[/color] wsSearch [color=darkblue]As[/color] Worksheet, wsResult [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] Found [color=darkblue]As[/color] Range, rngWhat [color=darkblue]As[/color] Range
    
    [color=darkblue]Set[/color] wsSearch = Sheets("Sheet2")
    [color=darkblue]Set[/color] wsResult = Sheets("Sheet1")
    [color=darkblue]Set[/color] rngWhat = wsResult.Range("A1")
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] IsEmpty(rngWhat) [color=darkblue]Then[/color]
        [color=darkblue]Set[/color] Found = wsSearch.Range("A:A").Find(What:=rngWhat.Value, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                                               
        [color=darkblue]If[/color] [color=darkblue]Not[/color] Found [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            Found.EntireRow.Copy Destination:=wsResult.Range("A5")
        [color=darkblue]Else[/color]
            MsgBox "No match found for " & rngWhat.Value, , "No Match"
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]