"Vlookup" macro that copies certain rows from one worksheet into another

[lovesjoyajm]

Hello all!
I am completely new to macros but I think they could help me in this case.

I have one spreadsheet (1) where the first column contains IDs. The other worksheet (2) has many more entries (much bigger) but each of the IDs from 1 is also in an entry of 2 (in addition to many more). I would like to copy ONLY the rows in 2 with the entries whose IDs match the IDs from 1 . Does that make sense...? I want to copy them into a new worksheet. I tried doing this one time by hand and recording it, but I don't know how to make it progress to the next entry.

Also, there are a few that are only in worksheet 1, and not in worksheet 2.

 

[Brianwarnock]

Re: "Vlookup" macro that copies certain rows from one worksheet into another

You do not need to use a Vlookup in the macro.

The code assumes data starting in Row 1 and Ids in Col A in both sheets adjust accordingly and also sheetnames.

Brian

Sub copyrows()
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim rownum1 As Long
Dim rownum2 As Long

lastrow1 = Sheets("sheet1").Range("A65536").End(xlUp).Row
lastrow2 = Sheets("sheet2").Range("A65536").End(xlUp).Row

rownum1 = lastrow1

Do
    rownum2 = 1
    Do Until rownum2 > lastrow2
    If Sheets("sheet1").Cells(rownum1, 1) = Sheets("sheet2").Cells(rownum2, 1) Then
    Sheets("sheet2").Rows(rownum2).Copy
       With Worksheets("Sheet3")
        .Rows("1:1").Insert Shift:=xlDown
        .Range("A1").PasteSpecial
       End With
       Exit Do
    Else
    rownum2 = rownum2 + 1
    End If
    Loop
rownum1 = rownum1 - 1
Loop Until rownum1 = 0

End Sub