VBA Code for Vlookup on different worksheets within the same workbook

Question

I am trying to write a vba script that will allow me to vlookup values from Sheet(3) to different Sheet(i) - and paste it on range"R2" on the Sheet(i) - I also want it to go to the end of the values in Column M on Sheet(i) [if this is possible]. I basically want to run through all the different "i" sheets on the workbook. Sheet (3) has all the data that needs to be copied on all the other "i" sheets.

I keep getting an error with my code below.

Sub CopyTableau1Data()

    Dim wka As Worksheet
    Dim wkb As Worksheet

    ShtCount = ActiveWorkbook.Sheets.Count

    For i = 9 To ShtCount

    With ThisWorkbook
        Set wka = .Sheets(i)
        Set wkb = .Sheets(3)
    End With

    Worksheets(i).Activate

    If IsError(Application.WorksheetFunction.VLookup(wka.Range("M2"), wkb.Range("E:T"), 14, 0)) Then
            wka.Range("R2").Value = ""
        Else
            wka.Range("R2").Value = Application.WorksheetFunction.VLookup(wka.Range("M2"), wks.Range("E:T"), 14, 0)
        End If

    Next i

End Sub

Answer 1

IsError does not work with Application.WorksheetFunction.VLookup or WorksheetFunction.VLookup, only with Application.VLookup.

It is faster and easier to return Application.Match once to a variant type variable and then test that for use.

dim am as variant

'are you sure you want wkb and not wks here?
am = Application.match(wka.Range("M2"), wkb.Range("E:E"), 0)

If IsError(am) Then
    wka.Range("R2") = vbnullstring
Else
    'are you sure you want wks and not wkb here?
    wka.Range("R2") = wks.cells(am, "R").value
End If

Note the apparent misuse of wkb and wks in two places. I don't see the point of looking up a value in one worksheet, testing that return then using the results of the test to lookup the same value in another worksheet.

Answer 2

You can use the following code:

Sub CopyTableau1Data()
Dim wka As Worksheet
Dim wkb As Worksheet, i As Integer
    ShtCount = ActiveWorkbook.Sheets.Count

    For i = 9 To ShtCount

    With ThisWorkbook
        Set wka = .Sheets(i)
        Set wkb = .Sheets(3)
    End With

    Worksheets(i).Activate
    wka.Range("R2") = aVL(i)
Next i
End Sub

Function aVL(ByVal wsno As Integer) As String
On Error GoTo errhandler:
    aVL =                 
Application.WorksheetFunction.VLookup(ActiveWorkbook.Worksheets(wsno).Range("M2"), 
ActiveWorkbook.Worksheets(3).Range("E:T"), 14, False)
errhandler:
    aVL = ""
End Function

When you try to check an error by isError, program flow can immediately return from the sub depending on the error. You could use on error in your sub to prevent that but this would only handle the first error. By delegating the error handling to a function you can repeatedly handle errors in your loop.

I assumed you wanted to use wkb.Range("E:T") instead of wks.Range("E:T").