VBA: VLOOKUP If Certain Criteria Is Met

[unknownymous]

Hello Gurus,

I'm currently have below code

DATA 1 Sheet:

ITEM 1	ITEM 2	ITEM 3	ITEM 4	DATA 4
001
002
003

Note: In cell E1, that is the Sheet where the data should be look up. Usually, manually filled as there are too many data sheet tabs.

In the code below, it will unhide the DATA 4 sheet (based on E1 cell value in Data 1 Sheet) and should lookup the data.

DATA 4 Sheet look like this:

ITEM 1	ITEM 2	ITEM 3	ITEM 4	NOTE
001	LOS ANGELES	1000	SHAMPOO
002	BALTIMORE	500	DETERGENT
003	NEW YORK	100	MISC

Sub VlookUp()

    Dim lookupSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lookupValue As Range
    Dim lastRow As Long
   
    Set lookupSheet = ThisWorkbook.Sheets("DATA 1")
    Set lookupValue = lookupSheet.Range("A2")
   
    Set targetSheet = ThisWorkbook.Sheets(lookupSheet.Range("E1").Value)
    targetSheet.Visible = xlSheetVisible

    lookupSheet.Range("B2").Formula = "=VLOOKUP(A:A, '" & lookupValue.Value & "'!A:E, 2, 0)"
    lookupSheet.Range("C2").Formula = "=VLOOKUP(A:A, '" & lookupValue.Value & "'!A:E, 3, 0)"
    lookupSheet.Range("D2").Formula = "=VLOOKUP(A:A, '" & lookupValue.Value & "'!A:E, 4, 0)"
 
    ' Copy formulas to last non-blank cells
    lastRow = lookupSheet.Cells(Rows.Count, "A").End(xlUp).Row
    lookupSheet.Range("B2:E2").AutoFill Destination:=lookupSheet.Range("B2:E" & lastRow), Type:=xlFillDefault


End Sub

= = = = =

When I run the macro, it is asking to open the file so I believe there is something to correct in my code.

Appreciate the help.

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.