Find Value and Copy/Paste Row from One Worksheet to Another

Hi,

I'm somewhat familiar with the vlookup function and how it works, but not advanced enough to integrate it the way I would like.

I have 2 worksheets with multi-column ranges. I would like to use vba to search a single column (A) in Sheet1 for a value in column (B) of Sheet 2. Then, I want to copy the row in which column (A) of Sheet 1 exists to the same row column (B) exists of Sheet2.

So, if Sheet1 looks like:

A B C
1 1 3
2 1 3
3 1 3

And Sheet2 looks like:

A B C
1 1 3
1 2 3
1 3 3

Then the Copy/Paste goes to Sheet2 to look like:
A B C D E F
1 1 3 1 1 3
1 2 3 2 1 3
1 3 3 3 1 3

I hope that makes sense.

You don't actually need VBA to achieve this.

Enter the following on Sheet 2 and copy down. Change the range row refs as appropriate

D1: =VLOOKUP($B1,Sheet1!$A$1:$C$3,1,FALSE)
E1: =VLOOKUP($B1,Sheet1!$A$1:$C$3,2,FALSE)
F1: =VLOOKUP($B1,Sheet1!$A$1:$C$3,3,FALSE)

Rgds

Thanks,

I'll give this a shot when I get to work. I'd still like to use vba so that I can put it in a macro, but I'll play with macro recorder for that.

Thanks Again.

I wanted to edit my last post but I couldn't find it. The find function seems to work much better for me now, but I'm having trouble incorporating it into a loop. Any help is always appreicated:

Sub Macro()
    Dim Arow As Long, Brow As Long
    Dim C As Integer
    Dim Answer As String

C = 1
Answer = Sheet1.Cells(C, 48).Value

On Error Resume Next
        Sheets("Sheet2").Select
        Columns("D:D").Select
        Arow = Sheet2.Columns(4).Find(What:=Answer, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
        
        
        Sheets("Sheet1").Select
        Columns("AV:AV").Select
        Brow = Sheet1.Cells.Find(What:=Answer, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row

        Sheets("Sheet2").Select
        Range(Cells(Arow, 1), Cells(Arow, 12)).Copy
        Sheets("Sheet1").Select
        Cells(Brow, 60).PasteSpecial

End Sub

I'm trying to Count C for the number of rows that are in my range.

Sorry for the confusion.