Is it possible to lookup for a value from another sheet in the same workbook?

Question

I have a workbook with multiple spreadsheets. One of the sheets is called "Master Filtered" and the other is called "MTL OR TOR" I want to fill in the column K of the "Master filtered" sheet with a lookup value from the "MTL or TOR" sheet in the second column. I wrote this piece of code but it is not working.

Sub MTL_OR_TOR()
    Dim AcctNb As String
    Dim result As String


    Worksheets("Master Filtered").Activate 
    Dim lastrow As Long
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row


    For G = 4 To lastrow

    AcctNb = Cells(G, 3).Value

    Set myrange = Worksheets("MTL OR TOR").Range("AA4:AB685") 'Range in which the table MTL or TOR should be entered
    result = Application.WorksheetFunction.VLookup(AcctNb, myrange, 2, False)

    Range("K" & G).Value = result
    Next

End Sub

Do you have any idea why is this code not working and how to fix it?

I was thinking maybe my error is in the line starting with Set myrange= Worksheets("MTL OR TOR") but couldn't figure it out.

What does not working mean? Are you getting an error? If so, on what line? — Josh Eller
It says "Active method of worksheet class failed And when I debug it highlights the row "Worksheets("Master Filtered").Activate " — Celine N
Add Option Explicit to the very top of the Module the code is in. This will force you to declare variables - right now you don't declare myrange, so I'd at least add Dim myrange as Range to the code. Also, since it's a static range (you're not changing it), I'd Set myrange ... outside of the For loop. — BruceWayne
Is the code in a module or in a worksheet? If it is in a worksheet, change to a module — Vityata

Michal Rosa Michal Rosa · Accepted Answer · 2019-05-30T00:38:45

Sub MTL_OR_TOR()
    ' Name your variables in a meaningful way and indicate their type
    Dim strAcctNb As String
    Dim strResult As String
    Dim lngLastRow As Long
    Dim lngLoop As Long
    Dim rngLookup As Range

    'Set your range and variables before you execute the code for readability
    Set rngLookup = Worksheets("MTL OR TOR").Range("AA4:AB685") 'Range in which the table MTL or TOR should be entered

    'Do not Activate or Select unless you really have to
    'Worksheets("Master Filtered").Activate

    With Worksheets("Master Filtered")

        lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        For lngLoop = 4 To lngLastRow
            strAcctNb = .Cells(lngLoop, 3).Value
            strResult = Application.WorksheetFunction.VLookup(strAcctNb, rngLookup, 2, False)
            .Range("K" & lngLoop).Value = strResult
        Next

    End With

End Sub

Is it possible to lookup for a value from another sheet in the same workbook?

1 Answers