Hi everybody,

I have the following code which is working perfectly on retrieving data from another sheet for VLOOKUP Wroksheet Function:

Private Sub Worksheet_Activate()

On Error GoTo MyErr

For C = 2 To 20
If Cells(C, 1) <> "" Then
Cells(C, 2) = Application.WorksheetFunction.VLookup(Cells(C, 1), Sheet1.[A2:B20], 2, 0)
End If
Next
Exit Sub

MyErr:
  If Err <> 0 Then
    MsgBox "No Data Available ", vbExclamation, "Missing Data"
  Else
    Resume
  End If

End Sub
I want to develop this code to retrieve data from another workbook >> I tried the following code but it didn't work well with me >> it always gives me an error .. Can You please help me in this case??

Private Sub Worksheet_Activate()

On Error GoTo MyErr

For C = 2 To 20
If Cells(C, 1) <> "" Then
Cells(C, 2) = Application.WorksheetFunction.VLookup(Cells(C, 1), ActiveWorkbook.Path & "\Test.xls" &  Sheet1.[A2:B20], 2, 0)
End If
Next
Exit Sub

MyErr:
  If Err <> 0 Then
    MsgBox "No Data Available ", vbExclamation, "Missing Data"
  Else
    Resume
  End If

End Sub