Find XLookup #N/A Errors and Copy Row and PasteValues into a new list on new worksheet

[kyddrivers]

I am tasked with finding an easy way to search a list, look for the #N/A returns from an XLookup, and copy and paste values the row containing the error into a new worksheet so all of the errors are in 1 list that can be audited.

The list looks like this:

Audits.xlsx
	A	B	C	D	E
1	Last Name, First Name	Email	Satus	End/Inactive Date
2	Smith, Jack	jsmith@xlookup.com	Active	1/0/1900	Blank
3	Dough, Jon	jdough@xlookup.com			#N/A
Lookup

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:E	Cell Value	>22510	text	NO

In this example, I need the code to look for the #N/A return in row 3, copy it and paste it into a new tab. Then return to the list and keep going until all of the #N/As are compiled in a new list.

I was thinking of using SpecialCells(xlFormulas, xlErrors) and sketched out some non-functional code like this.

Sub Lookup()

Dim LastRow As Long
Dim LastRow2 As Long
Dim i As Long

Worksheets("Lookup").Activate
LastRow = Cells(Rows.Count, 5).End(xlUp).Row

For i = 2 To LastRow
Worksheets("Lookup").Activate

If Range("E2:E" & LastRow).SpecialCells(xlFormulas, xlErrors) Then

Range("E2:E" & LastRow).SpecialCells(xlFormulas, xlErrors).Copy

'Paste into this sheet
   Worksheets("Audit Looup").Activate
   LastRow2 = Cells(Rows.Count, 1).End(xlUp).Row + 1
   Worksheet.Range("A" & LastRow2).PasteSpecial

End If
Next i

End Sub

Am I on the right path? Is there a better way to do this? Is this task even feasible?

Thanks in advance!

 

[DanteAmor]

Try this:

Note: Check the name of your sheets.

Sub copyerrors()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Set sh1 = Sheets("Lookup")
  Set sh2 = Sheets("Audit Looup")
  On Error Resume Next
  Intersect(sh1.Range("E:E").SpecialCells(xlConstants, xlErrors), sh1.Range("E:E")).EntireRow.Copy
  sh2.Range("A" & sh2.Range("A" & Rows.Count).End(3).Row + 1).PasteSpecial xlPasteValues
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[kyddrivers]

I changed xlConstants to xlFormulas, and it worked like a charm.

Thanks!