Macro help vlookup-> copy row

Warhammer

New Member
Joined
Oct 25, 2012
Messages
8
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
 
Try this...

Code:
[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]
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top