Hello All!

I am hoping that someone can help me out with this as I have spent a fair amount of time searching the Internet for an answer and what I find I cannot get to work exactly as I need it to. I have just enough Excel Macro Knowledge to be dangerous and I only request help when I really need it (which is why I don’t have many posts here). If you are looking at my code and think it’s not the most efficient, it’s because I work with what I know.

So, with that out of the way, I was wondering if someone would be able to provide a solution for this (it may be fairly easy and I am just missing it).

VLookup Help.jpg

What I need to do is put a Filter on Column B “Supplier Code” for “Supplier1” & “Supplier3”. I need to use the Filtered Data from Column A to C for a VLOOKUP in another Spreadsheet. So, I was thinking that I needed to create a Named Range (and maybe I don’t, this is what I don’t know) in order to use the Filtered Data in the VLOOKUP. This is what I am thinking the VLOOKUP would look like in the actual Spreadsheet (not in the VBA Code: =VLOOKUP(E2,Supplier_Number_Dealer_Cross.xls!VLookSel,3,FALSE)”

What seems to be happening is that the VLOOKUP is looking at ALL of the Data in Columns A to C, not just the Filtered Data. When I “Step Into” and I get to the “Range(rnVisibleVL1(1, 3), rnVisibleVL(1, 1)).Select” & “Range(Selection, Selection.End(xlDown)).Select”, I can see it selecting the Correct Information, but then when I execute the VLOOKUP, it returns Information that was not “Visible”.

Below is how I have it coded right now:

    
    Workbooks.Open Filename:= _
        "C:\Users\myusername\Desktop\Macro\Supplier_Number_Dealer_Cross.xls"
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$T$65500").AutoFilter Field:=2, Criteria1:= _
        "=SUPPLIER1", Operator:=xlOr, Criteria2:="=SUPPLIER3"

    Dim rnVisibleVL As Range
    Dim rnVisibleVL1 As Range

    Set rnVisibleVL = ActiveSheet.Rows("3:65500").SpecialCells(xlVisible)
    Set rnVisibleVL1 = ActiveSheet.Rows("3:65500").SpecialCells(xlVisible)

    Range(rnVisibleVL1(1, 3), rnVisibleVL(1, 1)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    ActiveSheet.UsedRange.Name = "VLookSel"
    Workbooks(NEWWB).Activate
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],[Supplier_Number_Dealer_Cross]Original!VlookSel,3,FALSE)"
Please let me know if you have any questions or need anything else from me. Thank you all very much for your time and help. I really do appreciate it.