VBA code for Vlookup to a different worksheet

[mikenelena]

Hello,

In an existing workbook I have a sheet ("Main") with 7 columns of data. Column 1 contains a file number.

I generate (by a query from Access) a new workbook & sheet containing the 8 columns of data. The first 6 of which are the same as in the Main workbook. I currently use a vlookup function to determine which claim numbers (and of course the corresponding data are on the new worksheet but NOT already in the Main workbook.

Presently I run this vlookup in Column G and copy the formula down. Then I highlight the column and past the values, filter by those values (I am after the ones with "N/A") and then create a new worksheet and copy/paste all of the N/A values in there. I would like to automate this with some VBA code. I have found and tested the following code which produces no errors, but no data results either. (I realize this particular code wouldn't do everything I need, but I figured it would get me started.) Can anyone offer any insight as to why this code is not working?

Any help would be MUCH appreciated! Thanks!!

Sub ImpFPQ()


    On Error Resume Next




    Dim Imp_Row As Integer
    Dim Imp_Col As Integer
    Dim Table1 As Range
    Dim Table2 As Range
    Dim cl As Range
    
    Set Table1 = Range("A2", Range("A2").End(xlDown))
    
    Set Table2 = Workbooks("Test.xlsm").Sheets("Main").Columns("A:G")
    
    Imp_Row = 2
    
    Imp_Col = 7
    
    For Each cl In Table1
        Cells(Imp_Row, Imp_Col) = Application.WorksheetFunction.VLookup(cl, Table2, 1, False)
        Imp_Row = Imp_Row + 1
    Next cl
    
    MsgBox "Done"
    
End Sub