VBA Vlookup multiple sheets

[Evie76]

Hi

Below I have a code which is great to do vlookup via vba on just one sheet. However i need it to look at 3 sheets in the same workbook. Can anyone help.

Sub Vlookup()

Set myrange = Sheet1.Range("B:E")

Set MeasureReference = Range("c3")

Set Status = Range("D3")

Status.Value = Application.WorksheetFunction.Vlookup(MeasureReference, myrange, 2, False)

Set ManagingAgent = Range("e3")

ManagingAgent.Value = Application.WorksheetFunction.Vlookup(MeasureReference, myrange, 3, False)

Set MARefNo = Range("f3")

MARefNo.Value = Application.WorksheetFunction.Vlookup(MeasureReference, myrange, 4, False)

End Sub

 

[Trebor76]

Hi Evie76,

Maybe this:

Option Explicit
Sub Vlookup()

    Dim myrange As Range
    Dim MeasureReference As Range
    Dim Status As Range
    Dim ManagingAgent As Range
    Dim MARefNo As Range
    Dim varSheet As Variant
    Dim wsSheet As Worksheet
    
    Application.ScreenUpdating = False

    Set myrange = Sheet1.Range("B:E") '<-Assumed this code name reference is static
    
    For Each varSheet In Array("Sheet1", "Sheet2", "Sheet3") '<-Actual tab names for the VLOOKUP - not their code name as above
        On Error Resume Next
           Set wsSheet = ThisWorkbook.Sheets(CStr(varSheet))
            If Err.Number = 0 Then
                Set MeasureReference = wsSheet.Range("C3")
                Set Status = wsSheet.Range("D3")
                Status.Value = Application.WorksheetFunction.Vlookup(MeasureReference, myrange, 2, False)
                Set ManagingAgent = wsSheet.Range("E3")
                ManagingAgent.Value = Application.WorksheetFunction.Vlookup(MeasureReference, myrange, 3, False)
                Set MARefNo = wsSheet.Range("F3")
                MARefNo.Value = Application.WorksheetFunction.Vlookup(MeasureReference, myrange, 4, False)
            Else
                MsgBox "there is no tab called """ & CStr(varSheet) & """ in the workbook.", vbExclamation
            End If
        On Error GoTo 0
    Next varSheet
        
    Application.ScreenUpdating = True

End Sub

Regards,

Robert