Look for #N/A sign then copy paste

[Nicolaf]

Hi,

I have data in two columns in Sheet1.

In column A between A1 and A20 I have a list of names.
In column B I have a VLOOKUP function (in cells B1 to B20) which looks for name in column A and returns a value.

I would like a macro that looks for all the #N/A in column B (cells B1 to B20) and if it finds #N/A then it will copy name in corresponding cell A into Sheet2 at the end of list of names in column B (let's suppose the names start from cell B1 Sheet2 going down).

How do I do that?



Nix

[Bob Phillips]

Public Sub PostErrorDetails()

    With Worksheets("Sheet1")
    
        .Range("A1:B1").AutoFilter
        .Range("$A$1:$B$20").AutoFilter Field:=2, Criteria1:="#N/A"
        .Range("A1:A20").Copy Worksheets("Sheet2").Range("A1")
        .Range("A1").AutoFilter
    End With
    
    Worksheets("Sheet2").Select
End Sub

[Nicolaf]

Great thanks!
Nic