MACRO VBA VLOOKUP based on Drop Down list

[drewyee]

Hello all, I am a fairly new VBA user. And I am trying to solve a problem that I have not quite been able to crack yet, but am hopeful that one of you excel experts will be able to help with. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Layout<o></o>
1) I currently have a drop down list of unique Customer ID’s on a worksheet called USER_PAGE. <o></o>
2) On a separate worksheet in the same workbook called LEAD_LIST, I have customer data. In particular fields Customer_ID, NAME, SALES_VOLUME, LOCATION <o></o>
<o> </o>
Problem: I want to create a VBA program that will fire when the user selects a customer ID from the drop down list on the USER_PAGE, and based on the customer id that they select, search for all matching customer ids on the LEAD_LIST sheet and then copy over all values that match onto the User page. <o></o>
<o> </o>
<o> </o>
<o>Here is what I have so far. </o>
<o>'Sets variables </o>
<o>Dim LEAD_L As Variant
Set LEAD_L = Worksheets("USER_PAGE").Range("C3")</o>
<o>'Starts the if then conditions.
If Worksheets("LEAD_LIST").Range("A1") = LEAD_L Then</o>
<o>
'THIS IS WHERE I AM STUCK, because the sheet might have customer id's that appear multiple times, and I want it to copy all over all the values and their associated fields and then paste them back onto the USER_PAGE.</o>
<o></o>
<o>'Copies over the entire row</o>
<o>Selection.EntireRow.Copy</o>
<o></o>
<o>End If</o>
<o></o>
Thanks in Advance…<o></o>

 

[stanleydgromjr]

drewyee,

Welcome to the MrExcel board.

This can be accomplished with Data, Filter, AdvancedFilter, with Criteria and Extract ranges.

See my Private Message to you (top right hand corner of MrExcel, Welcome, drewyee., "Your Notifications:".


Have a great day,
Stan

 

[drewyee]

I am sent out the sheet, not sure what exactly how to implement your above comments.

 

[drewyee]

Here is the worksheet that I am trying to create. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Basically I am trying to have this a VBA program create a copy over all the information on the LEAD_LIST page when based off the what the user selects from their customer id list.

Here is the code I have for creating and formatting the drop down list. I am still missing how to create a vlookup to pull the customer id's based off of that list to my first page.

Your help is much appreciated.

Thanks again. I also sent you a copy of the spread sheet yesterday, not sure if you got it?



Sub LEAD_LIST_TEMPLATE()
Application.ScreenUpdating = False

Dim name As String
Dim dater As Date
Dim wbMyBook As Workbook
Set wbMyBook = ActiveWorkbook
dater = Now
Dim iListCount As Integer

Sheets("LEAD_LIST").Visible = True
'******PROMPTS USER TO INPUT FIRST AND LAST NAME*********
name = InputBox("Please Enter Your Name", "LAST, FIRST")
MsgBox "Hello " & UCase(name) & vbCrLf & "It's " & dater
MsgBox "Are you ready to see your leads!!!."
Cells.Select
Selection.Clear
'******FITS NAME OF INPUT INTO ASSOCIATED FIELDS AND FORMATS CELLS*********
Range("C:C").Font.Bold = True
Range("C1") = UCase(name)
Range("C1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'*******SELECTING DATA FROM LEAD LIST SHEET & CREATING UNIQUE LIST*****
Sheets("LEAD_LIST").Select
Cells.Select
Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("A:A").Select
Selection.Copy
Sheets("Sheet1").Select
Columns("L:L").Select
ActiveSheet.Paste
Range("L1", Range("l65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True
Columns("l:l").Select
Selection.Clear
Range("C2") = "SELECT CUSTOMER NUMBER"
Range("C:C").EntireColumn.AutoFit
Range("C3").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("C3").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$AA$2:$AA$1000"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With<o></o>
<o> </o>
Thanks <o></o>