VBA Syntax for WORKSHEETFUNCTION.MATCH

Hello!

I have the code listed below. It works perfectly to match a cell content in the "Sylvia" worksheet to the cell content in worksheet called "BackEnd" and update the information


My Question:

I need to modify the syntax to where the sheet "BackEnd" is now located in a different workBOOK. I'm assuming the filepath needs to be included in this code but i'm not sure how to fit it into the syntax.



Sub UPDATE_RECORD_sylvia()
Dim lngRow As Long

On Error Resume Next

lngRow = Application.WorksheetFunction.Match(Worksheets("Sylvia").Range("E8"), Worksheets("BackEnd").Range("A2:A200000"), 0)
If Err.Number <> 0 Then
MsgBox "This Banking ID does not exist."
Exit Sub
End If
On Error GoTo 0

'Below: The first part of the Statement defines where the Banking ID is located in the BackEnd and states what column # it should land on.
'The second part of the statement defines the source sheet name, and the range-id to be copied over.

Worksheets("Backend").Range("a2:a200000").Cells(lngRow, 12) = Sheets("Sylvia").Range("E13")

End Sub

Any help would be appreciated!!!

Rob

You can not use WorksheetFunction across workbooks, instead use ExecuteExcel4Macro.
e.g

Please Login or Register  to view this content.