VBA - Lookup Entire Row from List

[Mayanwolfe]

Hello,

I am using VBA to create a user entry form where users select certain account numbers from a series of data validation dropdowns (this is already done and functions properly). The workbook also contains a second sheet with all accounts the user could choose from and their attributes. Here is where I need help: when users are done making selections on the first page, they will click a submit button and get back a new workbook with the account numbers they have selected, as well as all the additional matching attribute columns from the lookup sheet. The number of accounts in the user-submitted list may vary, so the range size should be flexible. However, there is always exactly one matching row for each account, so no need to deal with duplicates.

I know how to do this manually using INDEX/MATCH array formulas or by using VLOOKUP in conjunction with the COLUMN function, but I need a VBA solution as this will be part of a larger project to automate the creation of these user forms. So far, I haven't been able to find non-formula solutions to do this. I hope this makes sense. Here's an example.

If a user selects these account numbers on the form page:

10000020020

32000200203

10155563101

The code should lookup these accounts against the full list of accounts and attributes, and create a new workbook with the accounts and all the attribute columns associated with them:

ACCOUNT	CREATION_DATE	CLIENT_NO	TYPE	ACTIVE	NOTES
10000020020	10/24/19	500500	MANAGED	Y
32000200203	04/01/20	2301	NONMANAGED	Y	Under review.
10155563101	01/01/20	450054	MANAGED	Y

 

[DanteAmor]

Assuming your data is as shown on sheets 1 and 2 below:

Dante Amor
	A
1	ACCOUNT
2	120
3	220
4	320
5
Sheet1

#VALUE!

The following macro works with an advanced filter, so the "ACCOUNT" header must be the same on both sheets, as shown in the previous example.

Sub Macro5()
  With Sheets("Sheet2")
    With .Range("A1", .Range("A" & Rows.Count).End(3))
      .AdvancedFilter 1, Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
      .EntireRow.Copy
      Workbooks.Add
      ActiveSheet.Paste
    End With
    .ShowAllData
  End With
End Sub